Microsoft Excel add-ins
 

Office Excel add-insArticlesMicrosoft Excel fails simple math multiplication

Microsoft Excel fails simple math multiplication

If you are a user of Office 2007, you can observe this bug in Microsoft Excel 2007 yourself - try entering 77,1 and 850 into cells and then multiply them in a third cell. Check the proper multiplication product and then verify it in another program, for example, the standard Windows calculator.

At the end of September, an employee of the Microsoft Corporation posted a note in his private blog that during his work on Microsoft Excel 2007, he tried to multiply several number pairs. The actual product of each multiplication was 65535, however, the program displayed 100000 as the result of all the calculations. The first example of these calculations was 77,1 * 850.

Later on, the blogger himself and other users tried to multiply many different number pairs with a correct product of 65536, but again the program always showed 100000.

It was discovered afterward that such phenomenon appeared in all cases of multiplication operations of number pairs with a product of 65536.

Then it was discovered that this bug was limited to the multiplication products between 65,534.99999999995 and 65,535 and between 65,535.99999999995 and 65,536.

The Excel development team was duly informed about the bugs and began immediately to correct them.

In a few days, a description of this problem and its solution was presented in the official blog of Excel developers.

Specifically, it was stated: «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. 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».

Additionally, the developers mentioned that they were working on fixing this bug with all hands on deck and that a link to a fix would be available right upon its completion.

However, more than two weeks passed from the moment that the bug was found until the release date of the fix - the 10th of October. You can find it on the Knowledge Base site of the Microsoft Company (article KB943075). According to the developers` words, the current fix will be included in SP1 for Office 2007 (the date of pack release is not known yet).

Other Excel Issuies

© 2007 Office Assistance LLC. All rights are reserved.

Office Assistance LLC is the branch of MAPILab Ltd

About Office Excel | Partners | Excel resources
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Deutsche Version