It seems that any formula that should evaluate to 65,535 will act strangely.

Slashdot article references this newsgroup post.

Skip to content
# Daily Dose of Excel

## Haphazardly Posted Excel Information and Other Stuff

# 2007 Calculation Bug

##
29 thoughts on “2007 Calculation Bug”

### Leave a Reply

It seems that any formula that should evaluate to 65,535 will act strangely.

Slashdot article references this newsgroup post.

**Posting code?** Use <pre> tags for VBA and <code> tags for inline.

It’ll be interesting to see how long it takes Microsoft to acknowledge this publicly, and how long it takes them to fix it. This seems worse than the Pentium floating point bug (which was hardware, so Intel’s fault, not Microsoft’s).

I hear they’re going to deprecate arithmetic operations in SP1.

I guess this should be in next month updates, I cant see them waiting for a company to actually encounter the bug and sue them back because of mistakes in a calculation software

You might want to read that EULA that you agreed to (knowingly or not). If I understand the legalese, if you’re successful in your lawsuit the maximum amount you could collect is equal to what you paid for the software.

LIMITATION ON AND EXCLUSION OF DAMAGES. YOU CAN RECOVER FROM MICROSOFT AND ITS SUPPLIERS ONLY DIRECT DAMAGES UP TO THE AMOUNT YOU PAID FOR THE SOFTWARE. YOU CANNOT RECOVER ANY OTHER DAMAGES, INCLUDING CONSEQUENTIAL, LOST PROFITS, SPECIAL, INDIRECT OR INCIDENTAL DAMAGES.

And that applies even if Microsoft knew or should have known about the possibility of the damages.

Sure, the language is in the EULA, and I don’t understand legalese, but isn’t there a difference between MS not knowing about this and the gross negligence that they knew it existed and released the product anyway? Of course proving it and having the funds to go after MS are another story.

One could argue that you have to be responsible for yourself and not blindly rely on what the spreadsheet, but if I have to recalculate everything myself to make sure the spreadsheet works, then why use the spreadsheet?

Stupid program.

Don’t be so negative guys. Maybe it’s an Easter Egg.

Dick my comments linking back to the test code I have uploaded keep disappearing.

here it is with no link to see if that makes it:

I’ve put some test code on sos to help people work out if their wbs are affected. Only Excel 2007 is potentially affected, and only certain 65,535 and 65,536 combinations go wrong. No idea if there are other related issues.

If anyone spots problems or improvements leave a comment ta

cheers

Simon

Simon: Strange – they haven’t shown up in the spam queue. I have been having a few problems with comments lately, so maybe it’s related. Here’s the link to the test code

http://smurfonspreadsheets.wordpress.com/2007/09/25/excel-2007-calculation-bug-test-code/

Hurray!…one more reason to wait for office 14!

sam

Thanks Dick, my blog does the same to me

Sam I’d say one more reason to wait till SP1 of any critical product.

simon

“I hear they’re going to deprecate arithmetic operations in SP1.”

I don’t think they need to go that far.

If you format the 100,000 results as a date it gives the correct date for day number 65535, so we just have to learn to work in dates instead of decimals.

Is it a coincidence that 2003 # of rows is 65,536?

So this explains why I’m underpaid ?

The Microsoft response was rather quick, and it seems they have a good handle on the problem:

http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-update.aspx

Did anybody notice that when using the formula XIRR over a certain range where the result would be negative Excel returns 0.00%. This happens frequently, but not every time. This happens in various versions of Excel, they never seem to have corrected this. Other spreadsheets, like Open Office happily calculates the correct negative results. As I need to use this formula quite often, the lack of result is annoying. Any idea how this can be overcome in Excel?

Since were bringing up bugs, I learned this one the hard way at work. Memory leak when you use ADO to query an open workbook.

http://support.microsoft.com/kb/319998

Just to leave some detail in this thread:

http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/2bcad1a1a4861879/6850d1e3d2385246?hl=en&

Erich Neuwirth says:

Newsgroups: microsoft.public.excel

From: Erich Neuwirth

Date: Wed, 26 Sep 2007 21:37:50 +0200

Local: Wed, Sep 26 2007 8:37 pm

Subject: Re: Bug in Excel 2007

Reply | Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author

Since we have seen a lot of theories,

here is some condensed description.

65535-2^-35, 65535-2^-36, 65535-2^-37,

65536-2^-35, 65536-2^-36, 655356-2^-37

are numbers exhibiting this problematic behavior.

So if a floating point calculation has such a result,

(which is not visible because Excel does not display enough decimal

digits) then you get 10000 or 10001 displayed.

What also is interesting that in these cases

MOD(…,1) gives -2^(-35)… displayed in decimal as

-0.00000000002910383046 (with 20 decimal digits)

MOD(…,1) never should return negative values.

ROUND(…,1) in these cases also produce the wrong result (10000 or

10001), the this is persistent. Any further calculation using the result

of applying the ROUND function will be wrong.

So if the display engine used ROUND and/or MOD to create the displayed

value, that would be consistent with all the errors reported so far.

MS in the Excel blog at

http://blogs.msdn.com/excel/archive/2007/09/25/calculation-issue-upda...

states that the error occurs with exactly 12 floating point values, 6

near 65535 and 6 near 65536.

Earlier in this mail I listed 6 of them, but I could not find the other

6 yet.

Joel on Software provides insight and sanity in the fluster.

http://www.joelonsoftware.com/items/2007/09/26b.html

There’s more detail about the issue here, including when it becomes a real problem of wrong values rather than just wrong display:

http://veroblog.wordpress.com/2007/09/26/excel-2007-calculation-bug-displays-apparently-wrong-numbers/

From Joel on software:

“Q: Isn’t this really, really bad?

A: IMHO, no, the chance that you would see this in real life calculations is microscopic. Better worry about getting hit by a meterorite. Microsoft, of course, will be forced to tell everyone “accuracy is extremely important to us” and I’m sure they’ll have a fix in a matter of days, and they’ll be subjected to all kinds of well-deserved ridicule, but since I don’t work there I’m free to tell you that the chance of this bug actually mattering to you as an individual is breathtakingly small.

Q: Shouldn’t they be testing for these kinds of things?

A: I’ll bet that most of the numeric testing done on the Excel team is done automatically with VBA code. Cells containing this value display as 100,000, but from VBA, they’re going to look like 65,535 (since the number would be passed into the Basic runtime in binary, before the display formatting.) I’m sure there’s plenty of code to test display formatting, but with a bug like this that only happens on 12 out of 18446744073709551616 possible floating point binary numbers, it’s unlikely that any set of black-box tests would cover this case.”

So Joel is saying outright what the Microsoft people were clearly implying, that the probability of these numbers coming up is so ridiculously small that it is no problem at all.

I’m sure they are wrong. If the probability really was 12 in 18446744073709551616 the problem would not have been discovered in a billion years of computer use, never mind within a year of release date. The point is that in the real world some numbers are much more likely to occur than others, and a binary number that is equivalent to the product of a moderately sized integer and and a moderatly sized decimal with one figure after the decimal point is hugely more likely to occur than average. Why? Because balance sheets, invoices, bills of quantities and the like are literally full of just such numbers.

I haven’t got the information needed to work out what the actual expected occurrence of this number is, but my guess would be in the region of 1 in millions, rather than 12 in 18 billion billion.

So how serious is it? I’d say it was about as serious as you can get and still not be picked up by Microsoft quality control.

Certainly serious enough to check out the results of XL2007 in a different spreadsheet before sending them out of the door.

Doug,

I see your point. There’s a difference between saying 12 numbers out of 18446744073709551616 cause the error, and saying there’s a 12 in 18446744073709551616 chance.

However, I do think the chances of hitting those 12 numbers are still painfully small. The person who found the error was using it to multiply 850 by 77.1. In the world of money, It looks like he

was trying to figure out taxes. I can’t tell you how many times I’ve calculated my monthly base compensation by 77.7 or 77.5 or 77.1 to estimate my net after taxes. He just got lucky.

Sorry, I meant to say .777 .775 .771

Gee, Mike – I just thought you had a kick-ass bonus scheme…

Not to make light of the situation but if someone sued every time a calculation wrong… So there is an obscure bug. Lots of the spreadsheets that I see have obvious and common bugs and no one seems to care.

Why were they changing something that wasn’t broke? 2003 didn’t have this bug so why are they change the code that displays the values? It makes one wonder what flaw they had that nobody knows about that they were attempting to fix. Then again, they took a well respected design for Excel and totally trashed it for this 2007 crap. I’ve only just started using Excel 2007 and have found it very cumbersome.

Charles – They have revamped the whole recalc system to enable multi-threaded calcs, I suspect they spotted a possible optimisation and used it. As the error value is not reported in the .value or .value2 their tests probably missed it.

I totally agree the new UI is a disaster, but the Excel internals I reckon are on a good footing for the future now (well when they calc right ;-)).

Jim yes a few people have noted this bug pales into insignificance compared to the ones most users put in their spreadsheets.

Dick – I’m wondering if my comments get binned because I have no-script enabled?

cheers

Simon

Macroman

That ADO/open workbook memory leak has been there for ever, real shame because it would be a very powerful technique if it worked. It must be hard to fix.

cheers

Simon

Hey Simon,

It IS a shame. Made me look like I didn’t know what I was doing at work. I really don’t, I’m a newbie, but they didn’t have to know that. I did work around it though, thank goodness I found that article. …cheers.

It’s been fixed!

http://blogs.msdn.com/excel/archive/2007/10/09/calculation-issue-update-fix-available.aspx