It might surprise some to learn that Microsoft Excel is not infallible. Rather it has an annoying bug that has been in every version since inception. In some circumstances, it is incapable of adding 3 numbers together. Don’t believe me?
In a new spreadsheet in cell A1 enter the number -123.45. in Cell A2 enter 123, and in cell A3 enter .45. Now click in cell C4 and click the autosum button. It will come as a shock that it doesn’t in fact equal zero. Rather it comes to -2.8E-15.
If instead of using the sum function we use =A1+A2+A3 the answer is still wrong.
Interestingly if the order of the numbers is changed the calculation actually works correctly.
What does -2.8E-15 mean anyway?
-2.8E-15 is scientific notation. It means -2.8 moved 15 places to the right or a decimal point then 14 zeros followed by 28 as in -0.000 000 000 000 0028. It is close to zero, but it isn’t zero. Any calculation that was based on the result would also be out and would be carried through the workbook.
Why is it wrong?
Computers handle decimal numbers differently than integers. The error, is not in the calculation, rather it is a limitation of the way in which a computer can represent a number with a decimal point. When a few of these numbers, with limitations, in this case three numbers, are added, or subtracted together that limitation is compounded producing the result shown.
Why is it a problem?
Users that are not aware of scientific notation or basic users of Excel, or for that matter non-computer scientists can be easily confused when they encounter such as result. Many Excel users simply enter numbers into a spreadsheet that someone else has created, after all. Trying to explain to them that Microsoft have an error in the program can be very difficult for them to comprehend and accept.
So, is it actually wrong?
Yes. -123.45 + 123 + 0.45 equals zero. It does not equal -0.000 000 000 000 0028.
Is it a bug?
No. Microsoft intend for it to work that way, so it is not a bug.
What does Microsoft say? / Are they doing anything about it?
Well according to Microsoft, they implement IEEE 754 correctly, which is a specification which details how to calculate floating point numbers. They note the limitations on their website, but there is no solution planned.
In primary school, students are taught that when adding or subtracting decimal numbers, the maximum number of decimal places in the answer, can only be the maximum in any of the numbers to be added or subtracted. With multiplication and division there is another rule. Surly the specification IEEE 754 is lacking if it does not perform this check at the conclusion of the calculation, especially if it expects to have rounding errors.
Is there a workaround?
Microsoft recommend 2 workarounds, but only one of these actually work.
- Round the results of all calculations. That is wrap the formula =Round() around all of your calculations on a spreadsheet. I am absolutely gobsmacked by this. Not only does this smack of laziness, but they also expect organisations to teach all staff to do this.
- In Excel, the options, advanced setting, turn on Set Precision as displayed.
Hello, Microsoft. This doesn’t fix it! Try it and see.
Solutions
Fortunately, I have 2 other solutions.
Format all the cells
Apply a format to the entire spreadsheet to set the number of decimal points to be displayed. For example, if dealing with currency set all cells to display only 2 places. Note: This is acceptable for basic accounting sums, but if comparing two values then rounding will still need to be done on each value before the comparision.
If you work in a large organisation you could create an Excel template and make that the default template for the organisation. This should be suitable for most MS Excel users, and for those very advanced users that require something different, particularly higher precision, can change it themselves.
Use google sheets.
Yup. It doesn’t happen in Google Sheets. I guess they don’t let standards get in the way of a correct answer. Google Sheets also don’t have that mongrel ribbon.
Summary
When specification IEEE 754 was created in 1985, computer processors were slower than they are today. Today there is so much excess processor time that we have had silly paperclips that jump about in the middle of the screen. For many years we have had the spare processor cycles to check the result of the calculation for the correct number of decimal places according to the rules of basic maths. It is way past time to alter the specification to include this rule. Then Microsoft could follow the rule with a clear conscience.
Still Need Help with Excel?
If you still need help with Excel please call me or contact me using the form below.
