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%.
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
“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)
good points Jamie, said my the same myself on Andwers bolg here:
http://blog.livedoor.jp/andrewe/tb.cgi/5050284
tar
Spreadsheet Boo-Boos
Excel Design suggestions A few links to sites with advice on spreadsheet design and error detection.