# #Value Error using IF Statement

#### bemp87

##### Board Regular
Hi Community,

I have a formula that is leaving me stuck and I'm not sure how to solve it and hoping you can help.

Code:
``=IF(ISERROR(D50/D49),"BCB Auto-Fail",D50/D49),IF(AND(\$C\$14,\$C\$18,\$C\$22,\$C\$26,\$C\$37,\$C\$40,\$C\$44)="---Select---","N/A")``

Issue: the formula should be trying to calculate that IF D50/D49 results in an error (i.e. if both are 0 and returns DIV error) then it should return "BCB Auto Fail" if true, or if FALSE then divide D50/49, and IFcells C14 - C37 ="---Select---" then it should return "N/A".

I'm getting a value error indicating its the wrong data type, not sure where i'm going wrong.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Joe4

There are a number of errors with your formula. Your IF statements are not nested properly, and if you want to check if each of those cells is equal to "---Select---", you need to do each one explicitly, i.e.
Code:
``AND(\$C\$14="---Select---",\$C\$18="---Select---",\$C\$22="---Select---",\$C\$26="---Select---",\$C\$37="---Select---",\$C\$40="---Select---",\$C\$44="---Select---")``
However, I am a bit confused by the order of things you are checking. It looks like to me that you want one of the following three things returned in that cell:
1. "BCB Auto-Fail"
2. D50/D49
3. "N/A"

But the order of your conditions is not clear. For example, what if D50/D49 does NOT produce an error, but all those cells in your AND equal "---Select"?
What should it return in that instance, D50/D49 or "N/A"?

#### bemp87

##### Board Regular
Hi Joe & Community ,

What I would want to happen is IF C14, C18, C22, C26,. C37, C40 and C44 ALL = "---Select---" then cell A1 (for example) should return "N/A. Also I would want IF cell A1 returns an Error in dividing (D50/D49) to return "BCB Auto-Fail, otherwise if there is no error then return the actual result of D50/49. The order might not be accurate which is why I am reaching out, I feel like I have tried this every way possible and always end up with an error. Any guidance would be appreciated.

#### Joe4

What I would want to happen is IF C14, C18, C22, C26,. C37, C40 and C44 ALL = "---Select---" then cell A1 (for example) should return "N/A. Also I would want IF cell A1 returns an Error in dividing (D50/D49) to return "BCB Auto-Fail,
So, right there you are talking about two totally different checks:
1. IF C14, C18, C22, C26,. C37, C40 and C44 ALL = "---Select---" then cell A1 (for example) should return "N/A"
2.
IF cell A1 returns an Error in dividing (D50/D49) to return "BCB Auto-Fail"

The question is, which one takes precedence (the order of precedence matters!!!).
So, if BOTH those conditions are met, which one wins out? Do you want to see "N/A" or "BCB Auto-Fail"?
Or do you want to see BOTH of these statements in the same cell?

Replies
1
Views
60
Replies
2
Views
171
Replies
4
Views
78
Replies
6
Views
436
Replies
0
Views
144

1,128,173
Messages
5,629,162
Members
416,368
Latest member
PaoloC

### 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