Microsoft Excel: What You Know About Math?

Posted on September 28, 2007

This is an appropriate time to link to the What You Know About Math video. Christopher Null blogging for Yahoo's The Working Blog reports that Excel 2007 has a bizarre bug that generates errors when multiplying 850 by 77.1. If you multiply 850 by 77.1 in Excel 2007 you get 100,000 instead of the correct figure 65,535.

You can try it for yourself in Excel by multiplying 850 by 77.1. Excel will return 100,000 instead of 65,535. Similar operations that should return 65,535 will also come back incorrect, and there's a problem with 65,536, too. The problem is only present in Excel 2007; earlier versions of Excel are not affected.
Microsoft has traced the problem back to a floating point issue and how results are displayed within a cell in Excel. Microsoft says the calculation is actually done correctly, it's just that when it comes time to show the result on screen, Excel chokes. For example, if you multiply that "100,000" above by 2 and put the answer in a new cell, you'll get 131,070, not 200,000. However, this isn't reliable either: Try adding one and you get 100,001, not 65,536.

Microsoft's Microsoft Excel Blog explains that the error is a floating point error.

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.

Microsoft is working on a downloadable fix to resolve the issue. Bloggers have had fun with the error with words and phrases like "math-challenged," "doesn't add up" and "new math." jkOnTheRun jokes that he got a 50% raise thanks to Excel. Joel on Software offers a detailed post on the Microsoft Excel bug that makes it easier to understand.

MICROSOFT