Excel 2007 Calc bug

Harlan pointed this out in a comment on the fluent UI strategy post, having spotted it in the newsgroups.

If you type

=850*77.1 in a cell it results is 100,000 rather than the more traditional 65,535.

It seems many combinations that result in 65,535 have been updated to 100,000. (could it be a covert metrification attempt?)

Slahdot has some plausible explanations and some potential uses (try paying 655.35 quid into your bank account for example).

This looks a bit like a new-for-2007 optimisation that has a border case error, or an off by one error. How wide the impact might be I suspect we will never find out. I suspect this only affects in cell cal…. Whoooaa scratch that, this is a bit more serious.

Put any 2 numbers that when multiplied give 65,535 or 65,536 in 2 different cellsĀ  multiply them in another, in about 1 out of 10 times it will give the wrong answer (100,000 or 100,001).

I did this in 2003:

col A = row()

col b = 65,535/A1 or A2 etc

copy and paste (values – it did that by default??) into 2k7 and in col C put A*B and let the fun begin.

In col D put =C1=100000 – it never says true. And in most calcs it seems to know its really 65,535 or 6.

You can also do combinations of multiply and add to get wrong answers.

How serious is it? I don’t know – do you?

What caused it? I don’t know – do you? it does look like some bit diddling rather than a simple overflow though.

Am I glad I’m still using 2003? oh yes. Are you?

cheers

Simon

About these ads

6 Responses to “Excel 2007 Calc bug”

  1. Rob Bruce Says:

    I’m not usually a fan of media buzzwords or phrases, but the only thing that springs to my mind over this is “not fit for purpose”.

    More than ever I’ll be sticking to the ‘it ain’t out of beta until SP1′ rule when dealing with MS ‘upgrades’.

  2. Jon Peltier Says:

    So will SP1 deprecate worksheet calculation, or arithmetic operations?

  3. Jon Peltier Says:

    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

  4. Adam Vero Says:

    More detail about when this actually causes ‘real’ value errors rather than just display oddities here:
    http://veroblog.wordpress.com/2007/09/26/excel-2007-calculation-bug-displays-apparently-wrong-numbers/

    basically, anything which uses ROUND, or “precision as displayed” or exports to CSV may end with actually incorrect values in.

  5. JasonK Says:

    Microsofts response has been trying to convince people that its not serious because its just a “display error”. But that display error is what people make business decisions based on.

    Also, since things like rounding and exporting to CSV propagate the error, MS’s response of “we’re on it”. Doesn’t cut it.

    So far the only date I have heard as an even possible date for a bug patch is sometime in the 2nd or third week of October.

    My company for one, had to pull all our machines we had just rolled out for testing 2007. Luckily someone foresaw a problem could arise and didn’t just reimage the users machines but put new machines next to their old ones, just in case.

  6. Stephane Rodriguez Says:

    Read my article : “Microsoft Office XML formats? Defective by design”

    http://ooxmlisdefectivebydesign.blogspot.com/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: