Why am I seeing this bug using Sum in Excel?

Paul June 13, 2011
Pinterest Stumbleupon Whatsapp

I found an Excel calculation error using Sum. When some of the numbers are removed, the error disappears, although there is only zero’s behind the 3rd digit after the decimal place. Answer of sum in last row.

28.04000000000000000000000000
26.86000000000000000000000000
26.92000000000000000000000000
27.02000000000000000000000000
27.34000000000000000000000000
29.80000000000000000000000000
27.10000000000000000000000000
26.82000000000000000000000000
27.12000000000000000000000000
27.30000000000000000000000000
27.44000000000000000000000000
27.54000000000000000000000000
27.48000000000000000000000000
27.44000000000000000000000000
26.88000000000000000000000000
27.72000000000000000000000000
26.66000000000000000000000000
26.92000000000000000000000000
29.40000000000000000000000000
26.90000000000000000000000000
26.92000000000000000000000000
27.60000000000000000000000000
26.88000000000000000000000000
27.04000000000000000000000000
27.40000000000000000000000000
27.24000000000000000000000000
26.96000000000000000000000000
27.10000000000000000000000000
26.98000000000000000000000000
27.08000000000000000000000000
27.44000000000000000000000000
27.06000000000000000000000000
27.10000000000000000000000000
27.18000000000000000000000000
26.24000000000000000000000000
26.88000000000000000000000000
27.16000000000000000000000000
27.66000000000000000000000000
27.28000000000000000000000000
26.78000000000000000000000000
26.82000000000000000000000000
11.36000000000000000000000000
27.10000000000000000000000000
26.80000000000000000000000000
28.00000000000000000000000000
29.88000000000000000000000000
26.74000000000000000000000000
26.86000000000000000000000000
26.80000000000000000000000000
26.88000000000000000000000000
27.22000000000000000000000000
26.96000000000000000000000000
26.94000000000000000000000000
26.26000000000000000000000000
25.52000000000000000000000000
34.26000000000000000000000000
33.30000000000000000000000000
32.84000000000000000000000000
28.26000000000000000000000000
26.46000000000000000000000000
26.72000000000000000000000000
27.20000000000000000000000000
28.26000000000000000000000000
26.96000000000000000000000000
12.90000000000000000000000000
26.92000000000000000000000000
26.86000000000000000000000000
22.94000000000000000000000000
29.50000000000000000000000000
27.38000000000000000000000000
26.86000000000000000000000000
27.32000000000000000000000000
26.92000000000000000000000000
26.80000000000000000000000000
35.26000000000000000000000000
29.74000000000000000000000000
27.92000000000000000000000000
26.94000000000000000000000000
27.14000000000000000000000000
27.06000000000000000000000000
26.96000000000000000000000000
27.30000000000000000000000000
29.40000000000000000000000000
29.14000000000000000000000000
29.92000000000000000000000000
27.14000000000000000000000000
26.64000000000000000000000000
27.30000000000000000000000000
27.26000000000000000000000000
28.58000000000000000000000000
27.30000000000000000000000000
35.06000000000000000000000000
30.36000000000000000000000000
27.40000000000000000000000000
27.02000000000000000000000000
27.46000000000000000000000000
27.30000000000000000000000000
27.28000000000000000000000000
27.24000000000000000000000000
32.32000000000000000000000000
30.08000000000000000000000000
27.46000000000000000000000000
27.22000000000000000000000000
34.14000000000000000000000000
26.92000000000000000000000000
30.22000000000000000000000000
28.16000000000000000000000000
27.08000000000000000000000000
35.00000000000000000000000000
26.94000000000000000000000000
27.34000000000000000000000000
27.90000000000000000000000000
26.94000000000000000000000000
28.12000000000000000000000000
26.78000000000000000000000000
27.00000000000000000000000000
26.88000000000000000000000000
27.06000000000000000000000000
28.16000000000000000000000000
29.80000000000000000000000000
34.90000000000000000000000000
26.44000000000000000000000000
27.66000000000000000000000000
26.96000000000000000000000000
29.98000000000000000000000000
27.34000000000000000000000000
30.10000000000000000000000000
35.10000000000000000000000000
27.12000000000000000000000000
27.14000000000000000000000000
24.16000000000000000000000000
27.26000000000000000000000000
28.28000000000000000000000000
30.40000000000000000000000000
27.16000000000000000000000000
26.68000000000000000000000000
35.28000000000000000000000000
23.08000000000000000000000000
26.86000000000000000000000000
27.02000000000000000000000000
30.02000000000000000000000000
32.74000000000000000000000000
33.24000000000000000000000000
34.02000000000000000000000000
35.06000000000000000000000000
28.14000000000000000000000000
27.10000000000000000000000000
30.36000000000000000000000000
30.04000000000000000000000000
27.32000000000000000000000000
23.30000000000000000000000000
28.16000000000000000000000000
30.04000000000000000000000000
26.64000000000000000000000000
27.04000000000000000000000000
27.90000000000000000000000000
35.06000000000000000000000000
27.10000000000000000000000000
26.76000000000000000000000000
27.04000000000000000000000000
27.06000000000000000000000000
27.70000000000000000000000000
30.24000000000000000000000000
34.60000000000000000000000000
24.00000000000000000000000000
36.72000000000000000000000000
21.88000000000000000000000000
27.08000000000000000000000000
27.26000000000000000000000000
30.22000000000000000000000000
26.98000000000000000000000000
28.04000000000000000000000000
35.32000000000000000000000000
27.24000000000000000000000000
26.68000000000000000000000000
22.26000000000000000000000000
22.28000000000000000000000000
24.12000000000000000000000000
27.18000000000000000000000000
28.14000000000000000000000000
35.08000000000000000000000000
29.98000000000000000000000000
33.98000000000000000000000000
36.62000000000000000000000000
26.90000000000000000000000000
27.44000000000000000000000000
27.14000000000000000000000000
26.98000000000000000000000000
29.92000000000000000000000000
27.02000000000000000000000000
30.10000000000000000000000000
35.24000000000000000000000000
26.90000000000000000000000000
27.10000000000000000000000000
27.16000000000000000000000000
26.60000000000000000000000000
29.96000000000000000000000000
26.88000000000000000000000000
27.98000000000000000000000000
27.34000000000000000000000000
32.68000000000000000000000000
35.04000000000000000000000000
26.96000000000000000000000000
29.92000000000000000000000000
13.68000000000000000000000000
13.48000000000000000000000000
27.10000000000000000000000000
33.94000000000000000000000000
26.64000000000000000000000000
29.80000000000000000000000000
29.96000000000000000000000000
26.70000000000000000000000000
26.80000000000000000000000000
26.60000000000000000000000000
26.96000000000000000000000000
34.56000000000000000000000000
26.92000000000000000000000000
27.02000000000000000000000000
33.06000000000000000000000000
26.80000000000000000000000000
27.70000000000000000000000000
27.56000000000000000000000000
28.46200000000000000000000000
27.34000000000000000000000000
27.10000000000000000000000000
34.68000000000000000000000000
25.54000000000000000000000000
27.22000000000000000000000000
27.34000000000000000000000000
34.92000000000000000000000000
27.52000000000000000000000000
29.88000000000000000000000000
27.50000000000000000000000000
27.26000000000000000000000000
27.92000000000000000000000000
34.54000000000000000000000000
27.22000000000000000000000000
27.34000000000000000000000000
30.12000000000000000000000000
29.98000000000000000000000000
28.14000000000000000000000000
27.26000000000000000000000000
27.34000000000000000000000000
27.32000000000000000000000000
26.98000000000000000000000000
30.30000000000000000000000000
34.84000000000000000000000000
28.22000000000000000000000000
21.70000000000000000000000000
28.72000000000000000000000000
28.00000000000000000000000000
27.04000000000000000000000000
27.90000000000000000000000000
28.14000000000000000000000000
27.78000000000000000000000000
—————————–
7159.882000000010000000
=============================

I read on the Internet that Excel is supposed to be accurate up to 15 digits after the decimal place so above could be a bug?

Ads by Google

  1. Aibin Drug
    June 18, 2011 at 10:05 pm

    First select boxes with wrong numbers then right click and go to Format Cells. Then in Number menu go to the “Number” (which is under “General”) change decimal palces to 2 (or whatever you want).

    • Aibek
      June 19, 2011 at 7:01 am

      thank for the input :)

  2. Bruce Epper
    June 16, 2011 at 7:58 am

    Numbers are stored in a computer in the binary number system.  Conversion from base 10 to base 16, base 8, or base 2 will almost always introduce error since the computer is using a fixed amount of memory to store this new representation of the data.  This error becomes magnified when performing other operations on the data, hence the discrepancy you see in your result of the addition of these 255 values.  In this case, it appears that once you cross the 229 element line, the error becomes apparent.  This is one of the reasons why you should not display more decimal places than necessary for the data you are representing, i.e., if your initial data only used 3 decimal places, only display 3 decimal places for your results (as well as your input data).  You will not be increasing precision by displaying more decimal places in the result and you can end up confusing your audience when they see errors introduced by base conversion and rounding.

    • dfdf
      May 25, 2012 at 12:21 pm

      - 754,462,783,982,350,000
      754,462,783,982,349,000

      if you remove 2 zero's - they sum correctly - and interestingly this is when they are 16 chars long..
      if you add zero's the sum is even odder!

      thanks you've explained my bug..

  3. Aibin Drug
    June 15, 2011 at 12:04 am

    First select boxes with wrong numbers then right click and go to Format Cells. Then in Number menu go to the "Number" (which is under "General") change decimal palces to 2 (or whatever you want).