Office Watch readers come up with some truly strange arithmetic – Excel style. The conclusion: Excel definitely can’t add up – in fact it would fail an elementary school maths test.
Does Excel have an addition bug? You’d think that was a simple question to answer, you just insert the numbers and it either adds up correctly or not. Sadly it’s not that simple, even with simple numbers – but we think it’s a bug and a curious one at that.
We’ll try to present both points of view so that you can make your own judgement.
We’re indebted to all the readers who’ve written about the Excel addition bug. As you’ll see many readers had fascinating insights into the problem.
The Basic Problem
Phil N. came to us with the example of 8 two decimal place numbers which should add up to zero. Instead Excel shows a result with stray digits in the 12 decimal place and beyond.
That’s a problem if you try to test the result (eg does the sum of the values equal zero) because Excel thinks it isn’t exactly zero. It can be confusing if you’re only displaying the cell to two decimal places because it looks like the result is zero and you can’t work out why your test formula ( IF() ) isn’t working.
Check out our article Excel SUM anomaly for more details.
Readers found there own examples of similar problems, most involved addition where one of the numbers is negative. Possibly the simplest came from Stephen who got the error to show up in just three cells:
|
-1.23 |
|
1.12 |
|
0.11 |
|
0.00000000000000012490009027033000000 |
The problem has been around for a while with the same of similar issues appearing in Excel 2003 and 2007. We got varying reports of similar problems in spreadsheet programs from other manufacturers.
The Floating Point excuse
Many readers wrote (with varying level of politeness) to tell us how wrong we were. The fault lies with the processor chip, not Excel, because of the way the computer stores numbers and how the processor handles them.
The core problem is that decimal numbers can’t be recorded exactly in a computers binary code.
Reader Mike H explains
” 15 (the integer) is 00001111 in binary format, but the floating point number 15.0 is represented by the sequence of bits 0 10000010 11100000000000000000000. 15.1 is represented as 0 10000010 11100011001100110011010 “
Because the computer doesn’t store the exact number you expect, small errors will creep into calculations. Normally those errors show up at far greater precision than humans need so the difference doesn’t become apparent. But sometimes the problem does show up and affect the way Excel works.
At Office Watch we’re coming to the view that excuse isn’t good enough in the 21st Century but we’ll leave that debate for another time.
For this specific situation we can say that while floating point matters may be part of the problem it’s not the complete situation. As many readers said “It’s surprising to see these errors showing up in such low numbers and with a stray result in the 12th decimal place.”
Order should not matter
The main reason we think it’s an Excel bug is the discovery of a few clever Office Watch readers (Arna P. was the first then Paul S. and Stephen W.) who tested for something that should not matter – the order of the numbers being added.
If you sort the numbers in order (high-low or low-high) Excel changes the result to exactly zero!
At primary/elementary school you learnt that order doesn’t matter in addition – ie 2 + 3 is the same as 3 + 2 . We all know that but sometimes Excel doesn’t.
Grouping should not matter
Just like the order of addition should not matter to the result, it also should not matter if you group the numbers into two groups and add them separately. But that’s exactly what happens in Excel.
The result of the addition can vary just by re-arranging the SUM into two interim steps then adding those SUM’s together.
Notice how the split SUM’s added together in cell A14 works out correctly but adding five numbers in the first SUM makes Excel go wrong again.
The bad result in A19 is not only wrong but a different incorrect result from the direct addition in A9.
To return to elementary school for a moment … your teacher will have taught you that ‘ 2 + 3 added to 4 + 5’ is the same as ‘ 2 added to 3 + 4 + 5 ‘ . Yet again, Excel can’t always cope with simple arithmetic.
The Threshold theory
We’re indebted to Michael W for this analysis of the Excel addition bug which might explain both the sorting and grouping anomalies mentioned above.
“When taking the numbers and inserting them into the worksheet one at a time, and noting where the errors occurred, I found that the 5th and 8th numbers produced errors. Working with this, I found that the errors occurred where two conditions were met:
1. The sum crossed a threshold of any number equalling 1 followed by all zeros (1, 10, 100, 1000, 10000, etc).
2. The sum was at least a power of ten less than the original number.
For the original number, which was in the hundred thousands (-127551.73), the first error was produced when the sum crossed -10000. This was the 5th number. The 8th number also introduced an error to the sum because it crossed the thresholds of -1000, -100, -10, and -1 all at once.
Note: altering the list of numbers so that these thresholds are crossed individually produces an error in the sum at each threshold.
“
See Also
- How precise is Excel?
- Excel bug: Row Insertion and cell ranges
- Excel’s effect on economic policy
- Workarounds for Excel’s addition problems
- Excel addition problems – bug or not?
- Microsoft’s ‘Get out of jail free’ card for Excel
- Excel addition problems – Microsoft’s response
- Excel SUM anomaly
- What you see isn’t what Excel knows
- Excel calc bug fix
