27 September 2007

Interesting Excel 2007 Bug

I came across a blog entry from from Lori Pearce's "All Things SDK" blog regarding an Excel calculation bug. Turns out there is what appears to be a serious flaw in certain calculations in Excel. The actual calculation is irrelevant, it's the result that matters. Calculations with results that should be 65,535 and 65,536, in addition to about 10 other results produce very strange and highly inaccurate results. For example, if you multiple 77.1 x 850, the answer should be 65,525, but instead yields 10,000. However, the real problem, as explained by the Excel team (specifically David Gainer) is the following:

"This is an issue in a function that puts numbers in cells, so the values in Excel's memory are actually correct. Imagine A1 contains =77.1*850 ... Excel actually calculates the correct answer, and you can see that if you use VBA to check the value for A1 - it will be 65535. But in the function that takes that value and formats it to be displayed on the screen, for the values described above, there is a bug. Any calculations based off that cell will be accurate too."

Here's an example of the bug shown in Excel 2007

In all, there appear to be 12 results in 9 quintillion that produce flawed output in Excel.

From what I can tell, Microsoft is working on a patch and this bug does not affect earlier versions of Excel. Here's an entry from the Excel and Excel Services Team Blog with a more in-depth description of the issue and what they're doing to address it (also written by David Gainer).

The original blog entry for Laurie's post can be found here. More background on the generally difficulties in executing math functions in a computer can be found in the following blog entry on Wolfram's blog by Mark Sofroniou "Arithmetic Is Hard--To Get Right."

1 comment:

chaitanya sagar, Excel Expert said...

For more information about the result display issues you can check out this link.