Thread: Huge if statement, can't locate the issue

Howdy,

I wrote a huge if statement to work with a large chart. I've gone over it multiple times, but each time I hit enter, Excel tells me there's a problem with the statement and highlights the whole thing. Can someone take a look below and tell me if you see an issue?

Thanks!

=IF(C5=(OR("Desktop","Integrated Desktop")),(IF(AND(U5>=4,(OR(Z5>=2,AC5>=500))),"DT 4",(IF(AND(U5>=4,AM5>=4,AC5<500),"DT 3",(IF(AND(AM5>=2,U5>=3),"DT 2",(IF(AND(U5<=2,AN5=2),"DT 1",(IF(AN5=1,"DT 0")))))))))),(IF(C5="Notebook",(IF(AND(U5>3,AM5>=4),"NB 4",(IF(AND(U5>=3,AM5=2),"NB 3",(IF(AND(U5=2,AW5>13.3),"NB 2",(IF(AND(AN5=2,AW5<13.3),"NB 1",(IF(AND(AM5=1,AW5<=12.1),"NB 0","N/A"))))))))))),"N/A"))

Edit:
OK, I found a couple of errors and I now have the following:

=IF(C5=(OR("Desktop","Integrated Desktop")),(IF(AND(U5>=4,(OR(Z5>=2,AC5>=500))),"DT 4",(IF(AND(U5>=4,AM5>=4,AC5<500),"DT 3",(IF(AND(AM5>=2,U5>=3),"DT 2",(IF(AND(U5<=2,AN5=2),"DT 1","DT 0")))))))),(IF(C5="Notebook",(IF(AND(U5>3,AM5>=4),"NB 4",(IF(AND(U5>=3,AM5=2),"NB 3",(IF(AND(U5=2,AW5>13.3),"NB 2",(IF(AND(AN5=2,AW5<13.3),"NB 1",(IF(AND(AM5=1,AW5<=12.1),"NB 0","N/A")))))))))))))

Excel will accept this as a formula, but now returns #VALUE! instead of the NB or DT results in the formula. It sounds like I have text and math mixed here, but as far as I can tell I have parenthesis and apostrophes in the right places. Any help would be appreciated.

Hey Wilfite;

There appear to be potentially a few problems in there. I can highlight only a couple, and will give you a method to further debug on your own.

First and foremost, the OR operator is used to define full string that independently result in True/False results.

At the very beginning of your if statement, you use
=if C5=or(
Instead, you should use:
If(Or(C5="Desktop",C5="Integrated Desktop),

Now... stepping back for a minute, when you receive the #VALUE error, you get the wonderful opportunity to debug! If you click on the cell, you'll get the little [!] Indicator. Clicking on that, you have the option to show calculation steps. This will highlight the errors that caused the #VALUE problem... and it can also show you a breakdown of the calculations and their results for each component of the statement.