I've encountered a very simple but irritating problem with Excel. It's to do with how Excel calculates functions of cell values, and how this relates to IF conditions.

Its best to use an example.

In cells A1, A2, A3 and A4 type the following values -

20
0.4
0.1
6

In cell A5, calculate (A1*(A2-A3))-A4. This will evaluate to 0.
In cells A6, B6 and C6, type A5<0, A5=0, A5>0.
A6 should be FALSE, B6 should be TRUE, and C6 should be FALSE.

BUT now try typing (A1* (A2-A3))-A4<0, (A1* (A2-A3))-A4=0, (A1* (A2-A3))-A4>0.

This time the values will be FALSE, FALSE, TRUE. In other words, this time it evaluates the value as being >0, even though the two different methods condense to the same process.

This is incredibly frustrating as my spreadsheet requires these sort of conditions to be evaluated accurately, but I am not sure how Excel works in terms of evaluating formulas. If I want to do more complicated formulas, but using the same <0, =0, >0 evaluation as above, do I have to get Excel to compute each separate bit of the formula to get it to evaluate the conditions correctly?