# Trying to eliminate #VALUE! error from the formula by inserting ISERROR

#### fullspeed

##### New Member
Hello,
I am trying to eliminate #VALUE! error from the fomula below but I can't seem to figure it out. It is turning #VALUE! when there is no value in \$CQ\$7. So what I want to try is to insert ISERROR, hoping that that would get rid of #VALUE! and show the cell blank when there is no varibales available to do the calculation.

=IF(AND(\$F6="OPTION",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),IF(AND(\$F6="SAREQUITY",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),""))

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
=IFERROR(if(and(\$f6="option",\$ac6<=\$cv\$3),\$cq\$7-(\$af6/\$cu6),if(and(\$f6="sarequity",\$ac6<=\$cv\$3),\$cq\$7-(\$af6/\$cu6),"")),"")

You could add an IF to the start to check whether CQ7 has a value. If it does, go on with the rest of the formula, if not, put a blank.

=if(CQ7=0,"", IF(AND(\$F6="OPTION",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),IF(AND(\$F6="SAREQUITY",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),"")))

=IFERROR(if(and(\$f6="option",\$ac6<=\$cv\$3),\$cq\$7-(\$af6/\$cu6),if(and(\$f6="sarequity",\$ac6<=\$cv\$3),\$cq\$7-(\$af6/\$cu6),"")),"")

Thank you so much, This worked perfectly!!

Since CQ7 can either be blank or value (not 0), I changed it to
=IF(CQ7="","", IF(AND(\$F6="OPTION",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),IF(AND(\$F6="SAREQUITY",\$AC6<=\$CV\$3),\$CQ\$7-(\$AF6/\$CU6),"")))

and it worked perfectly. thank you very much.

Replies
15
Views
362
Replies
1
Views
183
Replies
11
Views
458
Replies
1
Views
351
Replies
1
Views
272

1,221,153
Messages
6,158,241
Members
451,477
Latest member
CWebbers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back