Submission + - Excel 2007 Multiplies Oddly
dsmall writes: "Excel Thinks 65,535 = 100,000
Microsoft Working To Fix Spreadsheet Problems
POSTED: 12:42 pm EDT September 28, 2007
SEATTLE — Microsoft Corp.'s Excel 2007 spreadsheet program is going to have to relearn part of its multiplication table.
In a blog post, Microsoft employee David Gainer said that when computer users tried to get Excel 2007 to multiply some pairs of numbers and the result was 65,535, Excel would incorrectly display 100,000 as the answer.
Gainer said Excel makes mistakes multiplying 77.1 by 850, 10.2 by 6,425 and 20.4 by 3,212.5, but the program appears to be able to handle 16,383.75 times 4.
"Further testing showed a similar phenomenon with 65,536 as well," Gainer wrote Tuesday.
He said Excel was actually performing the calculations correctly, but when it comes time to show the answer on the screen, it messes up.
Gainer said the bug is limited to six numbers from 65,534.99999999995 to 65,535, and six numbers from 65,535.99999999995 to 65,536 and that Microsoft is working hard to fix the problem.
This short summary is Copyright 2007 by The Associated Press. All rights Reserved
==================================================================================
Here is the actual blog entry at http://blogs.msdn.com/excel/ :
Tuesday, September 25, 2007 6:51 PM Calculation Issue Update
Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The Excel team would like to provide a description of the issue and explain what we're doing about it.
Background Yesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication — it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.
The Problem 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.
The Solution 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. We'll add another post once that's taken place with a link to the download.
Posted by David Gainer | 159 Comments
=================================================================== Note (from Dave Small): In my testing with a small (non-Excel) calculator:
77.1 X 850 = 65535,
10.2 X 6,425 = 65535,
20.4 X 3,212.5 = 65535,
Of course, 65535 = $ FFFF = $1111 1111 1111 1111,
and, 65536 = $1 0000 = $0001 0000 0000 0000 0000
(I usually separate out the binary into hex digits for readability.)
The blog entry seems convinced that the problem is in floating point conversion. I find myself wondering if the programmers simply have a .Word 16-bit value and don't realize the significance of these numbers.
I do not have Excel 2007 and cannot test it, but it would certainly be interesting to check numbers around 32767 and 4 billion (e.g., a .Long full of 1's).
Thanks,
— Dave"
POSTED: 12:42 pm EDT September 28, 2007
SEATTLE — Microsoft Corp.'s Excel 2007 spreadsheet program is going to have to relearn part of its multiplication table.
In a blog post, Microsoft employee David Gainer said that when computer users tried to get Excel 2007 to multiply some pairs of numbers and the result was 65,535, Excel would incorrectly display 100,000 as the answer.
Gainer said Excel makes mistakes multiplying 77.1 by 850, 10.2 by 6,425 and 20.4 by 3,212.5, but the program appears to be able to handle 16,383.75 times 4.
"Further testing showed a similar phenomenon with 65,536 as well," Gainer wrote Tuesday.
He said Excel was actually performing the calculations correctly, but when it comes time to show the answer on the screen, it messes up.
Gainer said the bug is limited to six numbers from 65,534.99999999995 to 65,535, and six numbers from 65,535.99999999995 to 65,536 and that Microsoft is working hard to fix the problem.
This short summary is Copyright 2007 by The Associated Press. All rights Reserved
==================================================================================
Here is the actual blog entry at http://blogs.msdn.com/excel/
Tuesday, September 25, 2007 6:51 PM Calculation Issue Update
Yesterday we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The Excel team would like to provide a description of the issue and explain what we're doing about it.
Background Yesterday evening we were alerted to an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. The first example that we heard about was =77.1*850, but it became clear from our testing as well as additional reports that this was just one instance where Excel 2007 would return a value of 100,000 instead of 65,535. The majority of these additional reports were focused on multiplication (ex. =5.1*12850; =10.2*6425; =20.4*3212.5 ), but our testing showed that this really didn't have anything do to with multiplication — it manifested itself with many but not all calculations in Excel that should have resulted in 65,535 (=65535*1 and =16383.75*4 worked for instance). Further testing showed a similar phenomenon with 65,536 as well. This issue only exists in Excel 2007, not previous versions.
The Problem 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.
The Solution 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. We'll add another post once that's taken place with a link to the download.
Posted by David Gainer | 159 Comments
=================================================================== Note (from Dave Small): In my testing with a small (non-Excel) calculator:
77.1 X 850 = 65535,
10.2 X 6,425 = 65535,
20.4 X 3,212.5 = 65535,
Of course, 65535 = $ FFFF = $1111 1111 1111 1111,
and, 65536 = $1 0000 = $0001 0000 0000 0000 0000
(I usually separate out the binary into hex digits for readability.)
The blog entry seems convinced that the problem is in floating point conversion. I find myself wondering if the programmers simply have a
I do not have Excel 2007 and cannot test it, but it would certainly be interesting to check numbers around 32767 and 4 billion (e.g., a
Thanks,
— Dave"
