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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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:

=ROUND(A1-A3+A2,2)

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.

Reply | Quote

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:

=ROUND(A1-A3+A2,2)

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.

Reply | Quote

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

ABS(result) <=  0.000 000 000 1 (1E-10)

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

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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

ABS(result) <=  0.000 000 000 1 (1E-10)

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

Reply | Quote