A bug lurking inside Excel 2007’s code determines some
pretty unusual answers from Microsoft’s product, involving calculation of
numbers around 65,535.
The bug was first signaled on Google
Groups on September 22, by Molham Sherry, who had previously tried to
calculate 850 multiplied by 77.1. The surprise couldn’t be bigger since in
Excel 2007’s opinion that equals 100.000!
Further confirmation about the serious flaw came from Excel
evangelists like Bernard
Liengme or Dana DeLouis, who also brought new details about other bugs.
According to Earl Kiosterud, who posted a reply to Sherry’s message, the 65,535
bug might have something to with the fact that the number has two bytes worth
of 1 digits in binary, but the fact that other multiplications (like 5.1*12850,
or 10.2*6425 or 212.5*308.4) give the same mind-boggling result in Excel 2007
worries experts even more. It appears that Excel 2000 does not suffer from this
vulnerability.
Microsoft knows about this issue for a long time and has an
explanation: “when you type or calculate a number between 32,768 and 65,535
that contains a decimal portion of .848, the number is evaluated and displayed
in the formula bar with a decimal portion of .8479999999. This problem may
cause results of calculations that use ten decimal places or more to be
incorrect by small amounts (by .00000000001). If the cell is formatted for ten
decimal places, the value that is displayed in the formula bar is also
displayed on the worksheet.”
Of course, following Microsoft’s consecrated tradition, the
aforementioned problem sits unsolved since August 17, 2005 and as you read
these lines Redmond’s engineers are still “researching the problem”…
Moreover, it appears that Excel 2007 inherits a 10+ defect
from its predecessors, which converts the numbers 40000.223, 41000.348 and
52000.723 into 40000.2229999999, 41000.3479999999 and 52000.7229999999
respectively.
Although the problem revolving around 65,535 had been
signaled since September 22, only three days later did Microsoft post an update
on its MSDN blogs: “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 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.
We take calculation in Excel very seriously and we do everything
we can in order to ensure that calculation is correct for all cases. We’ve come
up with a fix for this issue and are in the final phases of a broad test pass
in order to ensure that the fix works and doesn’t introduce any additional
issues - especially any other calculation issues. This fix then needs to make
its way through our official build lab and onto a download site - which we
expect to happen very soon.”
