Different Results with IF() (Excel 2003)

Topic

New Reply

I have attached a sample workbook that contains a problem that was brought to my attention, one that I haven’t seen before. Basically, the problem is that a calculation performed within an IF gives a different result than the same calculation performed without the IF. I used the Formula Auditing tool on both results and it stepped through the calculations and returned the same results that the cells show (i.e., different results in each cell). I am curious if any of the great Excel minds on this board has an explanation. Thank you all in advance

Reply | Quote

Replies

Since Excel stores binary numbers in binary form with a limited precision, rounding errors occur, both when converting to and from the decimal notation used for data entry and display, and during calculations.
Excel performs calculation with a precision of about 15 (decimal) digits. The numbers you add and subtract have 4 or 5 digits before the decimal point, so that leaves a precision of about 10 or 11 digits after the decimal point. The result of the IF formula is approximately -0.0000000000009, i.e. the 13th digit after the decimal point is different from zero. This is well within the expected precision.

Why then is the result of =B5-(B3+B4) exactly zero? Apparently, Excel rounds the results of direct calculations to a “nice” value. But if the calculation is part of a larger formula, this rounding doesn’t take place.

If you set the number format to display fewer decimal places, the displayed results will be equal.
If you’d like the result of the IF formula to be rounded, you can use for example

=IF(TRUE,ROUND(B5-(B3+B4),9),0)

Reply | Quote

Hi Hans,

Well, I knew that if anyone had the answer, it would be a Woody’s regular. Thank you kindly and thank you very much for your detailed and clear explanation.

Regards,

Reply | Quote