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»
