lundi 1 février 2016

Excel If Then with AND Operator - Troubleshooting Formula

I have a set of data that I need to use an IF statement that contain the AND operator and compares rows 1 to 2, then 3 to 4, etc. I am most concerned with the failure in my formula. It works (generates no errors), but doesn't return The value I expected. I've done searches on here and I have written my formula based on the advice others have posted, but I can't explain the discrepancy in the result. My data looks like this:

A   B   C   D
60  160 38  5
22  22  -38 3

I have formulas in Column E, row 1 and 2.

Row 1 formula looks like this and returns the expected value of 5:

=IF(C1>=12,5,IF((C1<=11*AND(B1>B2)),3,0))

Row 2 formula looks like this, but returns a value of 3 instead of the expected 0:

=IF(C2>=12,5,IF((C2<=11*AND(B2>B1)),3,0))

I assume I am doing something wrong with my AND. As it reads(to me), neither statement should be true and a 0 should be returned. The first part is true, C2 is less than 11, it is -38. The second part is not true, B2 is not greater than B1.

Aucun commentaire:

Enregistrer un commentaire