UPDATED: Quick! We need a calculator! - Excel 2007 bug

Summary:Excel 2007 fails remedial math ...

I don't usually cover random software bugs on Hardware 2.0 but since a few of you sent me links to this story I thought I'd post it for others.

[Updated: Sept 26, 2007 @ 4.30 am - The Excel 2007 team have been busy investigating and coming up with a fix for the Excel 2007 bug I discussed yesterday.  Now the team have released more information about the issue:

The Problem 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 (outlined below).  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.

The Solution We take calculation in Excel very seriously and we do everything we can in order to ensure that calculation is correct for all cases.  We’ve come up with a fix for this issue and are in the final phases of a broad test pass in order to ensure that the fix works and doesn’t introduce any additional issues - especially any other calculation issues.  This fix then needs to make its way through our official build lab and onto a download site - which we expect to happen very soon.  We’ll add another post once that’s taken place with a link to the download.

Interesting bug indeed.  Let's hope a fix is released soon.]

[Updated: Sept 25, 2007 @ 12.30 am - I just got this from a Microsoft spokesperson:

Microsoft recently learned of the flaw in Microsoft Office Excel 2007 that affects some calculations where the product should equal 65,535. We are currently in the process of developing and testing a fix for the flaw. Microsoft places a high priority on quickly responding to customer feedback and we are committed to finding ways to provide a better software experience.

I'll keep you updated.]

It appears that there is a serious bug in Excel 2007 affecting certain multiplications operations where the product should equal 65,535 but where it is instead displayed as 100,000.

Here's the bug in action:

excel2007bug_sm.jpg

Hey, this isn't on, I can make my own mistakes, I don't need to buy Office 2007 to do that. 

If you use Excel 2007 to do heavy number crunching I think it might be time to check those spreadsheets for this bug.  My advice is to go back to a previous version, OOo Calc or dust off that calculator.

Bonus points for anyone who can come up with a quick (funny) fix for this.  Here's mine:

Clippy: "It looks like you are trying to multiply two numbers.  Would you like to go get a calculator?"                                         Yes | No

Topics: Software, Microsoft

About

Adrian Kingsley-Hughes is an internationally published technology author who has devoted over a decade to helping users get the most from technology -- whether that be by learning to program, building a PC from a pile of parts, or helping them get the most from their new MP3 player or digital camera.Adrian has authored/co-authored technic... Full Bio

zdnet_core.socialButton.googleLabel Contact Disclosure

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.

Related Stories

The best of ZDNet, delivered

You have been successfully signed up. To sign up for more newsletters or to manage your account, visit the Newsletter Subscription Center.
Subscription failed.