Microsoft Acknowledges Floating Point Bug in Excel 2007

It's the type of bug that, when discovered twenty years ago on the world's leading spreadsheets of the time, generated controversy and sent thousands back to their software stores for refunds: Excel 2007 has a bug with the display of floating-point values, a Microsoft developer confirmed on his team's blog yesterday. BetaNews confirmed the bug's existence, which leads to some values in floating-point multiplication to be "rounded" to incorrect values.

The problem appears to be with certain kinds of pairs of values whose product is either 2¹⁶ (65,536) or 2¹⁶ - 1 (65,535). When one or both factors has floating-point precision but is only slightly fractional - meaning, it has perhaps one or two decimal places but is otherwise not "irrational," to borrow its mathematical meaning - Excel renders the product as 100,000 instead.

In BetaNews' tests, integer factors of 65,535 did not yield erroneous results, and nor did factors with long fractional trails. For example, the formula =88.3*742.18573 correctly rounded up to 65,535, even though in fact the true value is lower by an infinitesimally smaller amount. Users expect Excel to be able to round trivially unrounded values to more manageable amounts.

But it's this rounding feature that appears to be the problem, said Microsoft's David Gainer, not the calculation engine.

"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," Gainer wrote. "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)."

BetaNews verified Gainer's contention, though it resulted in a pair of cells that made it appear that 100,000 times 2 equaled 131,070.

Gainer then tried to dampen the issue by asking, what are the odds of anyone ever actually encountering this problem? "Of the 9.214*10^18 different floating point numbers that Excel 2007 can store," he added, "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."

Of course, Gainer's inquiry had already been answered by the fact that someone did discover this problem, albeit not in the beta test phase. His team is busy working on a fix, he said.