Excel can’t add up properly – is that an acceptable compromise or something Microsoft should fix?
We’ve been covering the Excel addition issues in a series of recent articles starting here.
To round out the coverage we’ll now take a look at the arguments pro and con so that Office Watch readers understand the limitations that are currently in-built to Excel and important for anyone using Excel. Beyond that we’ll have upcoming articles on the workarounds that you should now use in order to avoid problems with Excel. We’ll also look ahead to some possible solutions that could be designed into Excel. That’s if Microsoft stops its current state of denial about the problem and sees it as an opportunity rather than blaming ‘ignorant’ users.
- Is it a bug or not?
- What do we think?
- The IEEE 754 standard
- The normalization of deviance
- The Need for Speed
- Future Articles
- Why bother?
Is it a bug or not?
This issue of the Excel addition bug has generated an unusually high number of reader responses to us, and it seems that we have two broad camps:
· There are the people who understand much about floating point errors and the detailed workings of Excel. Some of them feel that Excel is behaving the way it’s supposed to and that each user needs to deal with that. This is also the line taken by Microsoft.
· On the other side is the ‘maths is maths’ or ‘zero equals zero’ camp. These people think that Excel should produce answers that are accurate according to the rules of traditional arithmetic – the same arithmetic that we all learn in elementary school.
Both sides have points in their favour. One group understands Excel as it is and accepts that, often in the interest of speedy calculation for large worksheets. The other side feels they are entitled to true accuracy.
We’ve also seen a ‘pass the buck’ mentality in private messages from the staff of both Intel and Microsoft. On Intel’s side they say that their chips have always been floating point based and any problem with that should be handled by the software. Microsofties have been telling us that Excel simply accepts the maths given to them from the processor chip and any errors are Intel’s fault.
If all this seems academic, remember that the 2-digit precision numbers in all the examples submitted by our readers are those used in most money calculations. Even simple worksheets for a personal budget can be affected, let alone complex modelling in organizations.
What do we think?
At first we were inclined to accept the floating point maths the way it is. After all, we’ve worked with Excel for well over a decade.
But as the reader responses came in we’ve had a change of heart.
We’ve been particularly stuck by the people who know about floating point issues but are still surprised that Excel can’t deal with addition of such low precision numbers and so few of them. There are examples of merely three numbers to 2 decimal places not correctly adding up.
As one reader has said ‘arithmetic is arithmetic’ or ‘zero is zero’ and another said: “Microsoft and Excel don’t get to redefine the rules of maths to suit their technical convenience”.
After much consideration I think they have a point – perhaps some of us in the computer business have become too accepting of floating point troubles and dealing with them?
When computers were less powerful and software less sophisticated, floating point issues was just one of the things we accepted as a necessary compromise- but we’ve got beyond that. CPU chips can do amazing things and at fantastic speeds compared to just a decade ago. Software is similarly more sophisticated and powerful. Computers are now sufficiently powerful and software is sophisticated enough to deal with the world as it is.
Computers are now in very broad use and far beyond the limited use of computer experts. For the most part Microsoft recognises that with smarter software and clever interfaces. Excel is a mass market product – it’s not sold as a specialised tool that requires technical knowledge to operate. Yet it seems that all users are expected to know about floating point errors (based on some less-than-helpful Microsoft Knowledge Base articles) and spend enormous amounts of time coping with these fundamental errors in Excel.
Customers are entitled to expect Excel to correctly evaluate formulas without the need for workarounds.
The IEEE 754 standard
Microsoft is quick to make self-serving use of technical standards when it suits them and this is especially entrenched when it comes to floating point errors in Excel.
IEEE 754 is a 25 year old basis for storing and calculating numbers on computers – it’s a fine and complex piece of work from many clever, unsung, people. But it’s not the law and not compulsory as Microsoft seems to be pretending most of the time.
Microsoft justifies the arithmetic errors in Excel by hiding behind a 25 year old standard when so much has happened in the computing world between now and them.
In this case the IEEE standard lets Microsoft get away with a lower standard of mathematical accuracy. Microsoft’s own development of Excel shows they are prepared to deviate from the IEEE 754 standard – there’s precedent for this within the company.
Microsoft already admits to straying from the IEEE standard in some cases and also introducing optimisations so that results are closer to ‘real world’ maths. Knowledge Base article 78113 has a heading “Cases Where We Do Not Adhere to IEEE 754 ” plus an optimisation for a specific case is documented under “Example When a Value Reaches Zero” . Obscure cases, admittedly, but it shows that Microsoft is only limited by IEEE 754 as much as it wants to be.
The normalization of deviance
The “normalization of deviance” is a fancy term for the gradual acceptance of compromises and problems by a company or community until they become the accepted standard.
Excel developers have, over time, a creeping acceptance of the floating point calculation compromise to the extent where it’s now accepted as unchanging reality. Floating point errors have always been in computers, so the thinking goes, so they must continue to be there for now and evermore, amen.
But really Excel is working in its own (self-serving) mathematical universe like some old science fiction story. In this alternate reality, adding up numbers with two digit precision gives you a result with over 15 digits to the right of the decimal. The order of adding numbers makes a difference to the answer you get. That kind of logic would get you failed from a basic maths class.
The Need for Speed
For a long time the focus in the computer industry has been on speed – faster chips, faster response from software especially for larger jobs (large documents and worksheets).
Maybe it’s time for a slight change of approach – with some attention given to proper mathematical accuracy and less reliance on the tired floating point excuse?
There’s an argument that spreadsheets would run slower if the floating point system was changed. We’re not sure that’s entirely true. It may be that arithmetic accuracy might make a marginal difference to the speed of very large spreadsheets but for the vast majority of users the speed change would be unnoticeable and the easier use plus accuracy would be obvious.
Arithmetically correct spreadsheets would not need many of the CPU consuming workarounds like ROUND(), INT() which add to the calculation overhead and development difficulty for existing worksheets.
In other words, some of the speed loss from accuracy could be made up by the less cumbersome worksheets and the faster development / maintenance time of worksheets that can be trusted for wholly accurate arithmetic.
Future Articles
Even as Microsoft doesn’t consider Excel arithmetic errors as a bug – that should not stop customers from holding a different view. After all, the customers are supported by thousands of years of human developments in maths and arithmetic. To say nothing of the awesome power of elementary school teachers who drill arithmetic into the skulls of each generation.
We’ll talk more about possible fixes that Microsoft could consider in a future article. As we’ll discuss, fixing the arithmetic in Excel would present a great opportunity for Microsoft.
We’ll also look, in detail, at what you have to do now to avoid the floating point errors that Microsoft won’t fix.
Why bother?
Whatever happens – we hope the current articles and discussion on Office Watch will help make people more aware of Excel’s limitations. We’re opening a debate on a matter that’s largely taken for granted. There’s still significant ignorance about floating point errors (another fact from the messages that we’ve received) so this coverage will help people make more effective use of Excel – that can’t be a bad thing.
