Spreadsheet Errors

Via the Best Practice Weblog, New Statistics on Model Error Rates

I consider myself pretty handy with a spreadsheet, but I’ll bet at least 10% of the spreadsheets I’ve used in the last year have an error in them. Here are a few things I do to help prevent errors:

  • Layout the spreadsheet so the inputs are readily identifiable and well labeled.
  • Use cell comments to explain the more cryptic formulas and not using Excel speak, but plain language.
  • When testing the formulas, use extreme examples. For example, what if the gross margin was negative, zero, or greater than 100%.
Posted in Uncategorized

4 thoughts on “Spreadsheet Errors

  1. The setup of a good spreadsheet is so important. I always believed that 85% of all errors are related to the setup of the spreadsheet. The other 15% is from fat fingers. A good spreadsheet can also control much of the fat fingers. I just saw this article at Microsoft and had to send to you. It relates to what are best ways to set up a spreadsheet. But, not even this article sets up a good spreadsheet.

    So what is a good spreadsheet and how do you reduce errors from it?

    I have my own opinion but its too long for a comment.

    http://office.microsoft.com/en-us/assistance/HA011082671033.aspx

  2. “Use cell comments to explain the more cryptic formulas”

    A good tip.

    Another good tip might be, avoid cryptic formulas, preferably in favor of self-documenting formulas e.g. using defined Names.

    I’ve learned that the most important aspect of development is maintenance. I used to favor a long formula in a single cell because I didn’t like using multiple columns when one would do (also I used to think they made me look ‘clever’). Trouble with this approach is you end up with formulas like this one:

    =IF(ROW(A1)=1,IF(ISERR(FIND(” “,$A$7)),$A$7,MID($A$7,1,FIND(” “,$A$7,1)-1)),
    IF(ROW(A1)

Leave a Reply

Your email address will not be published. Required fields are marked *