Excel calculation bug take 2 – not many hurt

I’ve been thinking about this and reading up at various places, trying to decide how big of a deal this is. Hopefully many of you will know I am quite heavily into the whole ‘spreadsheet quality’ scene. So here is my (surprising?) view – feel free to disagree.

I think its pretty much irrelevant. Basically fear of having a problem is probably a bigger issue that actually having a problem. (Eg someone mentioned stopping a 2007 rollout that is part way through – thats not good!)

Reasons why its a bad thing:

  • its an error!
  • It is mainly presentation but can be made to propagate in calcs
  • It gets exported in .csvs
  • We need to feel we can trust Excel – this shakes that.
  • single cell materiality is massive 65k – 100k is ~ 50% error

Mitigations:

  • 2007 market penetration is small
  • 2007 enterprise market penetration is V small.
  • It only affects a very small range of numbers.
  • In most cases it will not propagate – so you need to rely on one of the 2 erroneous numbers being in the face of a report, if they are intermediate calcs the error will probably not flow through to reports.
  • Most users probably make bigger blunders than this somewhere in their analysis/reporting process. Published material error rates in commercial s/s are in the 30-90% have error range – but the earth hasn’t stopped spinning.
  • No publicly reported cases in the wild (that I have seen – do you know different?)

Summary

Yep its not ideal, yep someone somewhere may have a significant problem, overall in the grand scheme of things this is unlikely to bring the sky crashing down. And yes of course MS need to get a patch out (soon would be good) and make lots of reassuring noises.
What do you reckon? too flippant? right sense of proportion?

Cheers

Simon

Advertisements

17 Responses to “Excel calculation bug take 2 – not many hurt”

  1. gobansaor Says:

    Simon, I think you’re right, overall the problem is not that serious. It would have been a different story if MS had gone to ground on the bug, but their quick response saved the day. Also, as you pointed out in a previous post, spreadsheets are boring, I’ve not seen any mention of this in the ‘civilian’ press/blogosphere; compare that with the widespread publicity that the Intel floating point problem raised a few years back.

    The other thing in MS’s favour is their now finely tuned remote update service, unlike a hardware problem (or a software problem in the days of CD/floppy distribution) such software issues can be fixed relatively easily. In the past I would have steered away from using V1.0 software but I’m far more likely to do so nowadays and with open source software I’m even willing to use RC or even beta status releases.

    Isn’t the interweb a great thing?

    Tom

  2. Jon Peltier Says:

    As Joel Spolsky says, better worry about being struck by a meteorite.
    http://www.joelonsoftware.com/items/2007/09/26b.html

    I wonder about the bad timing, though. People have said wait until SP1 before upgrading to any new release of a package. This came to light as SP1 is nearing release. Will people wait until SP2? Meanwhile Office 2003 SP3 is getting some (probably exaggerated) bad press.

    If you’re in that company that is considering a halt to its rollout of 2007, do you wait, or do you scrap plans to upgrade? What is such a great feature in 2007 that drives the need to upgrade? Other than it’s the latest and greatest?

  3. Harlan Grove Says:

    The concern I have is that this bug came about because the Excel team rewrote a bit of code that had been working before (both XL2007 and prior versions take floating point values in memory and render them as text using numerals and a few other characters). Lots has changed in XL2007, which means there are lots of other bits that have also been rewritten. How much faith do we put on the proposition that this was the only coding mistake in all the rewritten bits?

    However, if this is truly only a rendering issue, how should one interpret the following from David Gainer’s blog: “[…] to ensure that the fix works and doesn’t introduce any additional issues – especially any other calculation issues.” If it’s only a rendering issue, and the affected values in memory are unaffected, how could this introduce any other calculation issues? Well, I suppose there would be calculation issues when converting numbe

  4. Harlan Grove Says:

    [… numbe]rs to text, e.g., =123&” is no more”, but I doubt that’s what he meant.

    [@#$% touchpad mouse!]

  5. sam Says:

    Harlan – “How much faith do we put on the proposition that this was the only coding mistake in all the rewritten bits?”

    From Joel’s article on joelonsoftware.com

    “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.”

    sam

  6. Simon Says:

    Sam
    I think he’s being a bit harsh there (rose coloured specs?), all the Excel folks I’ve met have been smart and committed.
    I totally agree the user experience astronauts should be kept off anything important. I hated clippy but I’d choose that over their latest abomination any day (the ribbon).
    cheers Simon

  7. Jon Peltier Says:

    Simon –

    I agree. I’ve met a lot of the Excel team, and they are all smart and dedicated. Everyone thinks of Microsoft as huge and rich, and they are. But the individual groups do not have limitless resources. They have to do triage on the features they want to work on every release, and they have budgets and schedules to stick to. So they can’t get to everything.

    Also, some of what they have to do is not what they would prioritize first, such as all the work they had to do to make Excel compliant with the whole new Office Fluent UI (or e-Fluent). If they had been able to retain the existing UI, they would have had resources to make charting better and to make lots of other changes we’ve been clamoring for.

    Thismust be the influence of the “astronauts” you keep referring to.

  8. cleve Says:

    I come from a different perspective apparently, this is a big problem. I work for a very large financial and investment firm. Processing millions of trades per day. Many many of the spreadsheets that are used to produce the files for batch processing use the round function somewhere in them. The round function locks in the error.

    this gives the possibility of over charging a customer by $34,000+! The SEC does not care why the mistake was made. Only that it was made. their response to “It was Excel’s calculation mistake”, is going to be fine, we are still fining you ,and suspending that traders license whose name was on the trade. Go sue Microsoft yourself. YOU are responsible for your trades.

    We are one of the companies that had not started rolling out to 2007, but had started rolling out a few machines to test current processes with. they were pulled on Wednesday until MS finds a patch.

    We as a company have a responsibility to the customer that if we knowingly have even the slightest chance of preventing an error, we must take actions to do just that.

  9. Simon Says:

    Cleve – your co, like the vast majority are not currently using Excel 2007 in anger so I don’t see how it can be a problem? potential problem? sure, risk? maybe, worry? definitely. But you have a zero chance of suffering any bad at all as you are not using the software. Thats my point (and Joels), virtually no-one will suffer directly. Postponed roll-outs etc is more of an indirect cost I would say.

  10. cleve Says:

    I understand your point of view. But there are companies that have already rolled out 2007. Many IT depts. are so gung ho on Ms products that they feel they have to be at the forefront.

    Its because of those people that many of our branch offices already have 2007 running. I am in one of the subsidiary support companies (I said we are very large). I am not directly in the ‘Line of Command’. So I am having to write up reports and prove tests that show how it COULD cause a problem. then submit those up the line and fetch phone calls… yada yada yada…. yes it is an indirect cost, but it is costing us a lot of money in lost productivity by the people actually trying to keep our customers investments safe from even a potential error.

    I saw one person post on MS’s blog that “If its just 850*77.1, why is everyone so worried?”. Out of 65535 calculations I performed where the value should have been 65535, 10023 times it gave me the wrong answer.

    Thats a 15% chance if one of my customers has any combination of two values, number of shares and price, that equal 65535, that its going to be wrong. Thats a cost of risk. Risk we shouldn’t be exposed to from a product that has had 20+ years of development.

    (I am sorry if it comes across anything but conversational and friendly debate, thats all I am meaning by discussing).

    Have a great day guys & gals,
    Cleve

  11. Simon Says:

    Cleve
    fair points, there certainly is some sort of cost to this.
    I’d be very interested to hear if you find an actual occurence of an error, perhaps the code I posted the other day might help (depends on cell formatting).
    cheers
    Simon

  12. cleve Says:

    We pulled our back office systems in time. I hope I don’t see a error at the branches. But I might never hear about it. I am a simple VBA programmer/Instructor (Why I was involved in the rollout in the firstplace, I taught the class on intro to 2007 to 60 people an hour before I heard about the bug, talk about timing).

    When I expanded the calculations to 655,350 calculations that equaled 65535, I got over 100,000 wrong answers. Still around 15%. It doesn’t seem to matter how many tests I did there are so many possible combinations that equate to 65535 I was astounded. hadn’t ever thought about it before.

    Someone also found it wasn’t just simple A*B that can give the answer but any number of arguments A*B*C combinations also worked, any multiplier that equates to the given error range has the same 15% probability of being wrong apparently.

  13. Excel 2007 bug shows wrong answers to simple multiplications « Getting IT Right Says:

    […] has published some code which you can use to test your files for occurrences of the bug. He also discusses how important this bug is overall, in terms of actual impact to real users – noting that the number of people who have moved to excel […]

  14. cleve Says:

    Apparently some of you don’t work for very large (I mean huge international) corporations. You don’t just take someone else’s code snippet and distribute it throughout possibly thousands of people(I know we stopped rollout, I am talking those that didn’t or if we had not stopped).

    Our ‘Company’ is made up of multiple smaller companies, many of which have their own supporting IT groups. It would take almost as long as MS is taking to put out a fix to get the code/macro approved, arrange for official approved training on using that new code, etc… etc… etc… (yes they would have to train everyone, or else you open yourself up to the “I wasn’t trained properly, its not my fault” excuse)

    I hate corporate America, but thats how it works. Thats why I am having an uphill battle right now.

  15. Adam Vero Says:

    I agree, I don’t think Smurf will get much feedback from enterprise-sized clients, but there are many mid-sized firms which would be happy to run this test code (rightly or wrongly) in an effort to identify issues.
    One of the advantages of the way it is posted is that is is an open and transparent piece of code which can be read, understood and if necessary debugged by the person who chooses to download it. It is not some wrapped-up add-in with no visibility (which would decrease its take-up even further).
    Let’s just hope the fix arrives soon (and without waiting for sp1)

  16. Simon Says:

    Cleve I think you’d be surprised at some of our clients. I can’t remember the last non-global corp I worked with, and many other contributors are the same.
    Any competent VBA dev could have put that test code together in half an hour and run some tests on a selection of local files. And if it was as big of an issue as you think then that would be happening. That it isn’t is a sign that in the grand scheme of spreadsheet errors this is minor.
    I don’t know how familiar you are with the spreadsheet error research?
    This problem is trivial compared to the errors users add themselves. And thats my point.
    Adam yeah I never expected any feedback, add your guess at the fix release date to the sweepstake on one of the other posts.
    cheers
    Simon

  17. cleve Says:

    re: Simon
    Why invest the time and money developing a fix for something that should not have been broken in the first place. It is faster and more efficient to just roll back to existing technology that worked fine with no errors, no retraining, not extra code to run on every spreadsheet that anyone ever produces.

    having to run that code on Every spreadsheet that Every person produces throughout the day is taking two steps backwards. Why add the extra work, or take the chance on the one time one of your thousands of employees doesn’t remember to run it happens to be the one time it does get an error?

    No large corporation would implement that many extra steps to solve a problem that shouldn’t have cropped up in the first place. just roll it back and let people work the way that they are used to working, no extra steps to learn.

    Simplest solution, sometimes the best solution.

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: