#Value Error using IF Statement

[bemp87]

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.

=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.

 

[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.

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]

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?