Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Sterling, Virginia, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MS Excel calculation bug or Pentium processor bug (MS Office Excel 2003 v1

    I'm having a problem with excel calculating a simple formula and giving me the wrong answer. I'm expecting a zero, but instead excel returns a very small number.

    To duplicate this, open excel and try this:

    In a new workbook:
    type 818,562.54 in cell A1
    type -40,026.44 (negative) in cell A2
    type 778,536.1 in cell A3

    type the formula =A1-A3+A2 in cell A4

    Show the answer to 16 decimal places
    It should equal 0.00 but instead shows as: 0.0000000000582077

    Intel Pentium processor math flaw or MS Excel calculation error?

    Using the formula =A1+A2-A3 instead of the above formula gives
    the correct answer of 0.00

    Why the discrepancy?

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

    Re: MS Excel calculation bug or Pentium processor bug (MS Office Excel 200

    This is an "ordinary" rounding error. Excel stores numbers in binary format. When a decimal number is converted to binary format, there may be a slight rounding error. These errors may accumulate, depending on the sort of calculation. The order in which operations are performed matters.

    If the result of the calculation had been in the neighborhood of 1 or 10, you would never have seen the rounding error, but since the result is very near 0, the small discrepancy shows. If you set the mnumber format to display (for example) 2 decimal places, you won't see it either.

  3. The Following User Says Thank You to HansV For This Useful Post:

    m.i.k.e.r. (2012-03-18)

  4. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Excel calculation bug or Pentium processor bug (MS Office Excel 200

    As Hans said in his reply, this is due to normal rounding error when working with binary floating point numbers. You can get around this problem by changing your formula to:

    <pre>=ROUND(A1-A3+A2,2)
    </pre>


    You can change the second parameter to the Round function (,2 in the above) to the number of places that make sense for your calculations.
    Legare Coleman

  5. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MS Excel calculation bug or Pentium processor bug (MS Office Excel 200

    JackofAll:

    As Hans noted, this is inherent in binary representations of numeric values - there is no way to accurately represent -say- 0.13 as the sum of a series of "2^-n" fractions. The same is true of decimal representations - eventually you run into truncation errors if you are trying to add 1/3 + 1/7, etc. As the literature for Hewlett Packard calculators used to note, they could calculate with 16 decimal digit precision (IIRC) - "which exceeds the level of accuracy of most of the known physical constants of the universe" - but it still meant that pi or e were not "exact."

    There are some alternatives to deal with this - the first (and usually the best) is simply to ignore it - set the display for a useful number of digits, which will normally render these small differences invisible, and get on with what you have to do. Sometimes you can't ignore it - for instance, you may want to test a value for being equal to zero - and you perform a different calculation depending on the result of the test. In this case, you can either work around it by forcing all your calculations to use integers since integers are always accurately represented - unfortunately, intermediate calculations may involve fractional values, and any inaccuracy will be propagated through the following calculation stream. The other alternative is to determine the level of inaccuracy that might result from representation errors, and then instead of testing for zero, test for an absolute value that is less than the representation error.

    For example, Excel (2003) stores numbers with a 52-bit mantissa - two numbers that are different by less than one part in 2^52 will appear to Excel to be equal. Each floating point operation can (although it usually will not) trim off one bit of accuracy - so if you have 15 calculations involved you could be dealing with only 52-15 = 37 bits of accuracy - 2^-37 ~ 0.000 000 000 007 276 (7.2E-12). If you needed to test that your formula result was equal to zero, instead you could test for
    <pre>ABS(result) <= 0.000 000 000 1 (1E-10)</pre>

    The test will reliably identify "true" deviations from zero, while eliminating 'false positives' caused by representation errors.

    You can read more about Excel's precision limits at Knowledge Base: XL Floating-Point Arithmetic

  6. #5
    New Lounger
    Join Date
    Dec 2004
    Location
    Sterling, Virginia, USA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Excel calculation bug or Pentium processor bug (MS Office Excel 200

    thanks all. Makes sense. As Dean mentioned, I was testing the value to see if it was equal to zero, then applying another calculation to the cell as well as a conditional format. It looks like I'll have to use your workaround to trigger the next calculation.

  7. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MS Excel calculation bug or Pentium processor bug (MS Office Excel 200

    Sorry JackofAll - I should have mentioned the other alternative, which was touched on by (I think) Hans - just round the number you are testing to a number of significant digits sufficient to ensure accuracy. You can either do this inside your "IF" function, or in a separate cell. For example, if you trying to test whether A6 + B7 - C8 = zero, you can either put the formula =round(A6 + B7 - C8,6) in C10, and then test whether C10 = zero, or you can test: =if(round(A6 + B7 - C8,6) = 0,trueresult, falseresult) wherever in the spreadsheet it makes sense. This has the same effect as testing whether the absolute value is less than 10^-6 (actually, less than 5E-7, but that's a quibble)

Posting Permissions

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