IF AND Function Returning False

Question

A simple IF AND function only returns a false result. I've even tried using functions by replicating examples in my book, but they're still returning the false answer. For example: 

=IF( AND(B5="x",C5>=100), 1.5, IF(AND(B5="y",C5< 100), 1.4, IF(AND(B5="x",C5>=100), 1.3, IF(AND(B5="y",C5< 100), 1.2, 1.1)))) 

Always returns for 1.1 Even non-nested functions return for false. Any tips or solutions would be greatly appreciated, please and thank you.

Answer 1

Formulas are interpreted from left to right and a simple way to understand IF clause is to insert line breaks.

=IF(AND(B5="x",C5>=100), 1.5,

 IF(AND(B5="y",C5<100), 1.4,

 IF(AND(B5="x",C5>=100), 1.3,

 IF(AND(B5="y",C5<100), 1.2,

1.1))))

If the 1st IF becomes TRUE the result is 1.5 otherwise the other IF comes into affect.

But in this case the 3rd IF will never be TRUE, because it's the same same condition.

The same issue exists for the 2nd IF and the 4th IF, both have the same condition.

When we change the formula to this

=IF(AND(B5="x",C5>=100), 1.5,

 IF(AND(B5="y",C5>=100), 1.4,

 IF(AND(B5="x",C5<100), 1.3,

 IF(AND(B5="y",C5<100), 1.2,

1.1))))

The result might be what you expect:

Andreas.

Answer 2

Cannot really help you without knowing what the values are in B5 and C5.

And since you expect different results, the devil might be in details that we cannot really know unless you upload an example Excel file that demonstrates the problem to a file-sharing website (e.g. box.net/files), and post a downloadable URL.

The first question is:  did you copy-and-paste the formula exactly as it appears in the Formula Bar?

If you retyped it manually into your posting, you might have inadvertently corrected the mistake.

Otherwise, the function nesting looks correct.

If it always returns 1.1, that might mean that B5 is not "x", "y", "X" or "Y" exactly.  There might be surrounding spaces or non-breaking spaces (HTML ASCII 160) that you overlooked.  What does =LEN(B5) return?

Alternatively, C5 might not be <100 when B5="x" or >=100 when B5="y".  In other words, your logic might be flawed.