Funny old few days

Over the weekend I posted about spreadsheets being boring, and we generally agreed that was the case. And we agreed they weren’t likely to get big headlines in the general press.

On Monday I post a dig at the ribbon, comparing its contribution v the re-written multithreaded calculation engine.

On Tuesday the blog has its biggest day ever (3x previous max visitors) all looking for further info on the recently discovered calculation bug in the new multithreaded calculation engine in 2007.

And the mainstream media do indeed ignore the issue, in fact even the mainstream IT media seem to ignore it.

So the boringness is proven I suggest, all that remains is to decide which is the best/worst Excel 2007 feature:

  • Is it the broken UI or
  • Is it the broken calculation engine?

It pains me to say it but I think the ribbon did a great job. By keeping so many orgs from migrating to 2007 the impact of the calc bug has probably been much reduced. Thanks User Experience team!

I havent heard of any real world losses (or gains ;-) ) or problems caused by this bug yet. Have you?

Obviously having a spreadsheet that doesn’t appear to calculate accurately is sub-optimal for sure, but where are the real life stories of broken analysis and reporting it has caused? I guess as litigation is removed as an option by agreeing to the EULA most orgs will just bury any problems, like they do with security breaches and user errors.

What would be nice is if someone who used the code I posted got back to say they tried it on x live workbooks and it found y instances of the problem, or didn’t.

And finally a sweepstake on when/how this will get fixed:

  1. within days – hot fix
  2. within weeks – hot fix
  3. as part of SP1
  4. as part of SP2
  5. Blame the users?
  6. full scale denial?
  7. Deprecate calculation (Jon P)?
  8. Deprecate arithmetic (Jon P)?
  9. Other?

I am going to say within a month, separate from SP1. I am guessing this is high priority at Excel central so 2 days to isolate the prob, 1 day to fix it, a few days to prepare a binary patch, a week or so the test the fix in every language on every os etc etc. Date? Hot fix – 18th October.

Although Jons suggestion to deprecate arithmetic would probably work for the many that do the adding on a calculator and just type the numbers into the s/s to print it nice. If they claimed it as a security fix many people might put up with the ‘reduced functionality in certain scenarios’.

What do you reckon?

cheers

Simon

Advertisements

18 Responses to “Funny old few days”

  1. Rob Bruce Says:

    Part of me thinks that, unfortunately, the average end user spreadsheet is so badly designed that the odd 35000 here and/or there isn’t really going to compromise its accuracy that much.

    Rob

  2. Biggus Dickus Says:

    Within the week they have to have a patch. If not I will sqawk….

    I know that the chance of this impacting anyone is very small, it is a serious bug, politically bad, and it is possible that there are more numbers out there that just haven’t been found!

    How did anyone find this anyway ????

    Dick

  3. Simon Says:

    sadly I totally agree with you Rob, it probably makes half of them more correct.
    Dick 10 quid says more than 7 days – There is a lot of testing (and presumably some new tests ;-))

    oh and the prize for wining the sweepstake? no prize just the glory – get guessing.

  4. Harlan Grove Says:

    So who in their right mind will ever again pay their own money to upgrade to any brand new Office version?

    I think they have to isolate the problem, and I don’t think that’ll be all that easy since I suspect this bug arises from bitwise manipulation. As for how long a fix will take, kinda depends on why they tried to do what they did. From some of the examples in the newsgroups, it looks like they tried to introduce some ‘efficiencies’ for integer arithmetic. IOW, if the false 100000 DECREMENTED by 1 became 99999, I’d have more confidence that this was a simple screw-up quickly fixed.

    However, from what I’ve read so far, decrementing false 100000 gives 65534 while incrementing gives 100001, and false 100000 leads to different results when used as an argument to SUM vs AVERAGE. That makes it appear that there are some new .Value[2]-like cell properties being used. And if that’s so, and these new properties are integral to the recalc speed efficiency of the new calc engine, then my money wouldn’t be on a quick fix.

    But I gotta put my $0.02 (or is that $1.9999357892E-02 in new Excel math?) in: I’ll take early November as part of an expedited SP-1.

  5. Marcus Says:

    There’s a definition of taxation which says it is the process of plucking the most amount of feathers from a goose with the least amount of hissing.

    As Bigguss suggessted, sqawking gets noticed. I’d also agree with Harlan with a fix being provided in SP1. The timeframe will depending on how much hissing MS receive.

    P.S. Simon, you’re on fire. I can barely keep up with the posts. Keep up the great work.

    Cheers – Marcus

  6. Jon Peltier Says:

    “By keeping so many orgs from migrating to 2007 the impact of the calc bug has probably been much reduced.”

    That’s an interesting viewpoint.

    Given that SP1 is imminent, I am guessing that the fix for the calc error will be a separate hotfix. I also think it will be out in less than a month, maybe a couple weeks. This is a huge perception disaster (though I’m with Rob on the actual extent of damages).

    Given that SP1 is almost out and this problem has just surfaced, the old mantra of “Wait for SP1” might still have you upgrading prematurely. I suspect 2007 will end up, like 2002, being a “forgotten” version of Office. (My clients are either still on 2000 or have upgraded to 2003; only one is using 2002.)

  7. sam Says:

    May be this is a well know “bug” for most posters here….but to me this is more serious and probably more likely to occur in real life situation…. I simply cant believe this……

    As posted by Grant on http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/2bcad1a1a4861879/22be43c623402831?hl=en&

    The following error is apparent in Excel 97, 02, and 07.

    A1=120.05
    B1=120.04
    C1=A1-B1 (returns 0.01)
    D1=IF(C1=0.01,”Yes”,”No”) (returns “No”)

    The numerical result is actually
    0.009999999999990910.

    The error occurs with 120.10-120.01, and so on. …….”

    So now we cant rely on excel to do an accurate subtraction as well !!

  8. Harlan Grove Says:

    sam, yours is standard floating point rounding error. If you want exact decimal math, you need to use the Precision as Displayed option (and live with the errors the come with fixed point math).

  9. 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

  10. Simon Says:

    Thanks for the link Jon, its great to see them using the blog effectively to get info out there.
    Looks like I’m going to be a few weeks wrong in my guess. Good.

  11. sam Says:

    Harlan – I had tested this with precision as displayed – checked – same result – IF returns No

    Using – Excel 2003 SP2

    Sam

  12. Dennis Wallentin Says:

    As long as discovered bugs are not related to security then they hardly will make it to the big headlines. OK, MSFT needs to fix it asap as it otherwise will only create more badwill in addition to the bug itself.

    In the past a common approach was to skip version x.0 of softwares due to a common knowledge that they had some critical bugs. Today the release of SP-1 indicates that the most critical bugs have been solved.

    While reading Swedish computer magazines it seems that SP-1 of Vista and Office 2007 will increase the movement to Vista & Office 2007.

    Only the feature will tell us if 2007 will be a forgotten version or not. Most larger corporates have their own cycles time table while small and medium sized corporates tend to move quicker to new versions.

    Kind regards,
    Dennis

  13. Dennis Wallentin Says:

    I ment future and not feature in my above comment…

  14. Simon Says:

    Sam
    I can’t remember where it comes from but here is a maths/error fact.
    120.05 really means 120.045 – 120.05499999 or something
    If you use prec as displayed and increase dec places to 3 your IF will work right
    120.050 actually means 120.045 – 120.0549999 roughly
    We did it at joinery college about measurement, but I can’t remember to proper name?
    Margin of error? significant digit? rounding?
    Thats why in the uk most joinery items are listed to the nearest millimetre rather than metre.
    900mm = 899.5 to 900.49 = +/- 1 mm
    0.9M = 0.85 – 0.949 = +/- 10mm (ie probably wont fit)
    Sorry I could say this more succinctly.
    btw I always use if(abs(a) – abs(b) less than x where x is materiality…
    Cheers
    Simon

  15. Harlan Grove Says:

    sam, changing Normal style to #,##0.00, entering numbers/formulas as you indicated, the D1 formula returns Yes for me. However, if the cells had number format General, the D1 formula returns No. Precision as Displayed requires you avoid General number format.

  16. sam Says:

    Simon, Harlan …Thanks for the explanations….I need to be more careful with the IF in the future….

    Sam

  17. sam Says:

    Joel has a well written article on the bug…
    http://www.joelonsoftware.com/items/2007/09/26b.html
    I particularly liked the last para :-)

    “And let’s face it — do you really want the bright sparks who work there now, and manage to break lots of perfectly good working code — rewriting the core calculating engine in Excel? Better keep them busy adding and removing dancing paper clips all day long”

  18. Hirmenlibredvcs Says:

    smurfonspreadsheets.wordpress.com – cool sitename man)))
    ————————
    internet signature: http://dewat.ru/

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


%d bloggers like this: