Adding IF statement to formula is causing math error

[JimboMon52]

I'm a long-time Excel user, but I've never run into this issue before and I'm hoping someone might provide some insight. I'm using Excel for Mac 2011, v14.4.1.
I just started a new spreadsheet and created a simple subtraction formula of three dollar and cents amounts (e.g.: =B1-C1-D1) and it provided me the correct answer --- looking for an answer of zero. But when I added an IF statement to the formula (e.g.: =IF(A1="due",0,B1-C1-D1), Excel now yields an answer that is off by 2.84217E-14. None of the data values are calculated; all are entered directly. A1 thru E2 are the only used cells in the entire spreadsheet! And this problem duplicates on multiple spreadsheets. The error does not show up if the source values are whole-dollar amounts, only if they all contain any cents. I am very baffled. Why is adding the IF function affecting the results? Yes, I realize I could use the ROUND function, but I shouldn't have to. My worry is that if adding the IF statement is affecting this simple spreadsheet, what is it doing to my much more complex sheets and results?! The file is attached.
Thanks so much for any insight.
Jim

 

[Debraj]

Hi Jim



http://support.microsoft.com/kb/214118

Follow step's from there to solve the issue..

BTW.. did you tried..
=IF(A2="due",0,B2-(C2+D2))

 

[JimboMon52]

Hi Jim

View attachment 6510

http://support.microsoft.com/kb/214118

Follow step's from there to solve the issue..

BTW.. did you tried..
=IF(A2="due",0,B2-(C2+D2))

Debra, thanks for the reply. Your suggestion of enclosing the subtractions in parenthesis allows it to calculate correctly, but it still doesn't explain why the floating-point rounding error ONLY occurs when the formula is part of an IF statement. Standing alone, outside of an IF statement, it calculates without any floating-point rounding error.

 

[Jon von der Heyden]

It's not only IF. The same is true with:

=SUM(B1,-C1,-D1)

and even

=(B1-C1-D1)*1

The problem seems to exist when any coercion takes place - but I cannot explain why.