Excel 2007 calculation bug fix released after two weeks

A fix for the Excel 2007 calculation bug affecting results around 65535 and 65536 has been released in the last few hours. The Excel team blog post says:

As of today, fixes for this issue in Excel 2007 and Excel Services 2007 are available for download…We are in the process of adding this fix 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).

Microsoft knowledgebase article KB943075 discusses the fix and gives the usual details for what versions and sizes the updated files should have after the fix. The version number of Excel.exe is altered from 12.0.6024.5000 to 12.0.6042.5000. Now read that again – yes, easy to miss the difference from ’24’ to ’42’ if you look too quickly. (NB: you may have a different version, mine is at 12.0.6024 after installing the security update as per KB936509, as far as I can tell.)

The download for the fix for Excel 2007 (33Mb exe file) is linked from the Excel team blog as well as from the KB article. The blog post also has links for Excel Services 2007, both 32 bit and 64 bit.

Excel 2007 bug shows wrong answers to simple multiplications

This is a follow-up post to my earlier one about a bug in the way Excel 2007 displays the results of certain calculations. Read that one first of you have not already done so.

A few people in the comments thread in the Excel team blog post about the bug seem to have some misconceptions about the seriousness of the problem. Some have asked how often it is likely to come up, implying that they think it is vanishingly unlikely. This seems to be particularly those who have misunderstood that the example of 850*77.1 is only one simple example which is easy to remember and to type, but there are several more simple ones as well as thousands of other combinations which lead to the buggy result (due to floating point rounding errors in the calculation hitting a result which is sufficiently close to 65,535 to cause the false display of 100,000). Nine examples are shown in the screenshot below, and in a table you can easily copy and paste to try this for yourself.

» See some simple examples and find out more about functions which reproduce the error, and which ignore it safely»

Excel 2007 calculation bug displays apparently wrong numbers

A bug has been found in Excel 2007 and Excel services 2007 which appears to calculate certain results incorrectly. In fact, the stored value of the result is correct, and other calculations based on that result will calculate correctly. The only error is in the display of the number, not the internal calculation. This is, of course, still a problem for anyone who is reading the values on screen or on a printout, or exporting them to other programs (see further down in this post).

According to the article on the Excel team blog about this bug:

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.

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, it is important to note that most calculations which result in numbers near to or equal to 65,535 and 65,536 will be absolutely fine. It is only through some very specific oddities about how floating point numbers work that you will get one of the 12 situations where this bug occurs. If it does, you will have cells that read “100,000” rather than the correct answer. Anything else in Excel which you base on those cells will be correct.

You can add to them, multiply by them, show conditional formats such as colour scales or icon sets, even draw charts with those values and Excel will correctly handle the real, underlying value and not the displayed one. Macros or external programmatic methods of retrieving the cell’s contents also return the true stored value.

»Read the rest of the post to find out how this bug will bite you»