More on the Excel 2007 Muliplication Bug
More on the MS Excel 2007 bug (see my last post). It seems that Microsoft have a fix, and one of their guys has posted information about the bug on the Microsoft MSDN website. One statement they made caught my eye as being a bit specious:
‘The key here is that the issue is actually not in the calculation itself (the result of the calculation stored in Excel’s memory is correct), but only in the result that is shown in the sheet. Said another way, =850*77.1 will display an incorrect value, but if you then multiply the result by 2, you will get the correct answer (i.e. if A1 contains “=850*77.1”, and A2 contains “=A1*2”, A2 will return the correct answer of 131,070).’
Err, that’s not good enough. It doesn’t matter what the underlying cell contains - if it shows the wrong number, that’s what people will read, and they’ll use the wrong number. ‘Harrumph, in my day when a computer printed out an answer, that was what you thought the computer was saying was its answer’.
Should have used Mathematica.
Postscript 10th October: Microsoft have posted a fix.
Multiplication Bug in Microsoft Excel 2007
I didn’t believe this when I saw it at Slashdot (original Usenet post here), so I tried it myself. Open a copy of Excel 2007, and in any cell enter the formula =850*77.1. The answer should be 65535 (check it in you calculator), but it actually comes out in the Excel with the value 100000. A number of other calculations yield the same problem - try it out for yourself.
Pretty amazing, isn’t it? Multiplying two numbers together in Excel does not work for particular choices of numbers. (By the way, Microsoft Excel 2003 does not show this behaviour.)
This is obviously a big problem to anyone who uses Microsoft Excel for any kind of calculation work. Of course, you might think that relatively few calculations will throw up this error, i.e. just those calculations with floating-point numbers that yield 65535 as the answer, but that’s not the point. It’s just shoddily-written software, and therefore should not be used for serious analysis, especially for scientific or engineering work (imagine designing an aeroplane wing or bridge strut with this kind of mistake potentially creeping in). Actually, this bug is a problem for the very thing Excel was written to do, which is to allow you to multiply columns of numbers together! Hopefully my bank will use it to work out my balance, and I’ll come out 35 grand up - all I need is to get exactly 65535 pounds in credit….
With simple bugs like this in the program, you should start to wonder what other problems there might be in the way Excel does things, and well you should. This issue reminds me of a recommendation I read some time ago that Microsoft Excel (any version) should not be used for statistical analysis, since many of its functions are deficient; the difficulty here is that some of those deficiencies are subtle and therefore likely to not be noticed by the average user.
This is worse than not having the algorithms available at all, as it encourages people to believe that the algorithm that is provided works properly. I’ve had it said to me like this: ‘well, the authors of Excel wouldn’t have put the function into the application if it didn’t work, would they?’ Well, they did, and the algorithms are poor. Not convinced by the multiplication bug above? Then take a look at these articles:
- On the accuracy of statistical procedures in microsoft Excel 2000 and Excel XP
- Wikibooks article on numerics in Excel
- Vanderbilt discussion on Excel statistics
- American Statistical Association article on statistical software
I hope that convinces you of the basic piece of advice I repeat here: don’t use MS Excel for anything serious, if you use it at all.
Postscript: Wolfram have an article on the underlying issues in computer arithmetic. Worth reading.