Explaining the Excel Bug
This item ran on the Joel on Software homepage on Wednesday, September 26, 2007
By now you've probably seen a lot of the brouhaha over a
bug in the newest version of Excel, 2007. Basically, multiplying 77.1*850, which should give you 65,535, was actually displaying 100,000.
Before I try to explain this, I should disclose that I did work on the Excel team, but that was thirteen years ago. I haven't been there for a long time. I don't even think I know anyone on that team any more. I'm just trying to explain the bug a little bit as a public service.
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.
....