Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Different Results with IF() (Excel 2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Different Results with IF() (Excel 2003)

    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)

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Different Results with IF() (Excel 2003)

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •