Man, that’s a maddening little thing to catch:
Microsoft Corp. yesterday confirmed that Excel 2007, the newest version of its market-leading spreadsheet, returns incorrect calculation results in some cases.
The math bug first surfaced Saturday on Microsoft’s own Excel support newsgroup when a user named Molham Serry reported that when he multiplied 850 by 77.1, Excel 2007 returned 100,000 rather than the correct 65,535. Others on the newsgroup quickly took up the standard, eventually posting more than 120 messages to the thread. Among their findings: Other calculations claimed 100,000 was the correct answer.
Yesterday, the Excel team offered a mea culpa posting to a Microsoft company blog. “The majority of reports were focused on multiplication, but our testing showed that this really didn’t have anything do to with multiplication,” said David Gainer, lead project manager for Excel. “It manifested itself with many, but not all calculations in Excel that should have resulted in 65,535. Further testing showed a similar phenomenon with 65,536 as well.”
Joel Spolsky, formerly of Microsoft and now of FogBugz, guesses how it happened:
The first thing you have to understand is that Excel keeps numbers, internally, in a binary format, but displays them as strings. For example, when you type 77.1, Excel stores this internally using 64 bits:
0100 0000 0101 0011 0100 0110 0110 0110
0110 0110 0110 0110 0110 0110 0110 0110
The display is showing you four characters: “7”, “7”, “.”, and “1”.
Somewhere inside Excel is a function that converts binary numbers to strings for displaying. This is the code that has the bug that causes a few numbers which are extremely close to 65,535 to be formatted incorrectly as 100,000.
Spolsky also acknowledges how hard it is to test for this condition:
I’ll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they’re going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I’m sure there’s plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it’s unlikely that any set of black-box tests would cover this case.
Well, with an automation tool and enough time, you could build a script that ran through all possible values; even though this would take a lot of time for something like Excel, you could do it in the matter of weeks that you have for testing. I mean, big companies have actual test cycles, don’t they? The trade literature indicates they’re not as crazy as my actual experience indicates.
The key, in any event, is given a good enough automated tool and time, QA should find these problems. However, QA rarely gets the first and almost never gets the second.
It seems the tech team was converting the numbers into octal or some such before performing the validation, and 8 and 9 were returning invalid because they’re not valid octal numbers or something; still, it was an embarrassing oversight.
Had we but enough world and time and a decent automated test tool, we would have tried all 365/366 combinations; however, from that time on, we always tried 8 or 9 when encountering an edit box (!) for birthday entries, and encouraged designers to use drop-down lists for birthdays.
Given the constraints of time and the business, the best you can do to supplement your regular boundary analysis sorts of tests is to pay attention to variable types and their max values and mess around with them in addition to the maxima and minima for your data entry. And good luck.