Apparently, Excel 2007 cannot multiply correctly if you want to know the answer to
850*77.1
The correct answer is 65535 but it gives 100,000, instead. This bug was first reported in Microsoft Excel Group last week and Microsoft has already confirmed it. A quick fix will be available soon.
David Gainer from Microsoft explained the problem (excerpt from his blog):
This issue was introduced when we were making changes to the Excel calculation logic in the Office 2007 time frame. Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases (outlined below). 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).
So what, specifically, are the values that cause this display problem? Of the 9.214*10^18 different floating point numbers (floating point on wikipedia) that Excel 2007 can store, there are 6 floating point numbers (using binary representation) between 65534.99999999995 and 65535, and 6 between 65535.99999999995 and 65536 that cause this problem. You can’t actually enter these numbers into Excel directly (since Excel will round to 15 digits on entry), but any calculation returning one of those results will display this issue if the results of the calculation are displayed in a cell. All other calculation results are not affected.
For full details, click here.
Joel, a former Excel developer, and now, running own software house, also explained the problem from technical point of view. It’s a good read for those who curious about this issue.
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″.
For full details from Joel, click here.
[tags]Microsoft Excel 2007[/tags]
