Results 1 to 6 of 6

20041216, 13:54 #1
 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 =A1A3+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+A2A3 instead of the above formula gives
the correct answer of 0.00
Why the discrepancy?

20041216, 14:03 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

The Following User Says Thank You to HansV For This Useful Post:
m.i.k.e.r. (20120318)

20041216, 15:59 #3
 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(A1A3+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

20041216, 16:09 #4
 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 52bit 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 5215 = 37 bits of accuracy  2^37 ~ 0.000 000 000 007 276 (7.2E12). 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 (1E10)</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 FloatingPoint Arithmetic

20041216, 20:12 #5
 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.

20041216, 21:28 #6
 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 5E7, but that's a quibble)