Wednesday, October 10, 2007

Fix available for Excel 2007 calculation issue

Few days back The Excel team Blog alerted us about an issue in Excel 2007 (and Excel Services 2007) involving calculation of numbers around 65,535. It was really critical, let us see using an example, the first example was =77.1*850, Excel 2007 would return a value of 100,000 instead of 65,535. Some other similar examples were, =5.1*12850; =10.2*6425; =20.4*3212.5 .
The Excel team testing showed that this really didn't have anything do to with multiplication, and similar phenomenon occurred with 65,536 as well. And it was only in Excel2007.

They said, Specifically, Excel incorrectly displays the result of a calculation in 12 very specific cases.(given 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).

Of the 9.214*10^18 different floating point numbers, 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.

Screenshots of the said calculation before Fix is applied:

As said earlier, 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. Here as shown, multiplication by 2 gives correct result.Today the Excel Team released a fix-Calculation Issue Update (Fix Available) for it. So those using Excel2007 or Excel Services should apply this fix. Download them from the following locations:

Excel 2007: http://download.microsoft.com/download/6/1/3/61343075-aa12-4152-a761-fccc16d6cef4/office-kb943075-fullfile-x86-glb.exe
64-bit Excel Services 2007: http://download.microsoft.com/download/c/d/c/cdcccd84-86cd-4199-b01c-1df2dac66534/office-kb943076-fullfile-x64-glb.exe
32-bit Excel Services 2007: http://download.microsoft.com/download/c/d/c/cdcccd84-86cd-4199-b01c-1df2dac66534/office-kb943076-fullfile-x86-glb.exe

KB Articles have been posted as well:

Excel 2007: http://support.microsoft.com/default.aspx/kb/943075/
Excel Services 2007: http://support.microsoft.com/default.aspx/kb/943076

This fix will be added to Microsoft Update so that it will get automatically pushed to users running Excel 2007 or Excel Services 2007. Additionally, the fix will also be contained in the first service pack of Office 2007 when it is released (the release date for SP1 of Office 2007 has not been finalized).

Screenshot after the Fix applied:

The Fix is for Excel 2007 is nearly33MB download, Must be wondering why it takes 33MB to fix this issue. It may be due to as it seems, contains every modified file in full, so that product media to install the patch is not required. You can see the KB article which shows updated files which are quite large.
So everybody can update it first, even if they are not having the Office2007 media .
Also I just viewed the .exe under winzip, as you can see it below. And after the update I checked it under Add/Remove programs (KB943075)

No comments: