#Value Error using IF Statement

bemp87

Board Regular
Joined
Dec 10, 2016
Messages
100
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 10, 2016
Messages
100
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
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?

 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,208
Members
416,229
Latest member
mohammadmihdi

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top