nested if returning a false instead of a number.

[mark gardner]

I have data in 4 adjacent cells A1,B1,C1 and D1.

ex: A1 is 9, B1 is 4, C1 is 1, and D1 is 1.

In E1 I have this formula:


=IF(AND(C1=1,D1<>1),IF(A1>B1+0.4,1,0))

It is supposed to check to see if C1=1 and D1 is not equal to 1 and if so, check to see if A1 is more than .4 greater than B1. If the statement is true it should return a 1, if false, it should return a 0.

The problem is it keeps giving me a 'false' message instead of a 0 when the statement is not true.

can someone tell me how to fix this? I don't understand why it won't carry through to the else part of the if-then-else statement when it is false.

thanks

 

[Joe4]

That's because you haven't entered a FALSE argument in for your outer IF (only the inner nested one).

Perhaps you want:

=IF(AND(C1=1,D1<>1),IF(A1>B1+0.4,1,0),0)

 

[Joe4]

Better yet, I don't think you need to nest the IF statements. You can include more than two conditions for an AND statement. So just check all three at once, i.e.

=IF(AND(C1=1,D1<>1,A1>(B1+0.4)),1,0)