Greg asks about error detection in spreadsheets; a great topic, I think. We’ve all read the studies that say 95% of all spreadsheets have errors. I tend to think those numbers are exaggerated, but I may be wrong. Speaking for my self, I think my error rate is well below 95% (like 85%).
There are probably some great error-catching techniques out there. Sadly, my number one error catching technique is happenstance, that is, I just happen to see an error. I think that’s true of most people.
I’m trying to think of how I implement error checking into my spreadsheets. When I crossfoot, I build error checking into the formula. One technique that I use quite a bit is the formula that returns nothing if there’s no error. For instance, in my balance sheet assets must equal liabilities and equity. I have a formula that looks like
The font is red, and huge. There’s no mistaking when my balance sheet doesn’t balance because it practically hits me in the face. The 10 months of the year when there’s no problem, the formula is unobtrusive.
I have an estimate spreadsheet where bids are entered and summed. One of the error checking techniques I use there is a Change event. I use a SelectionChange event to store the value of the currently selected cell. If that cell changes by more than 10%, a warning is displayed. Ten percent is a pretty big difference and it usually indicates a keying error, although not always.
The warning for the above technique is that a comment is added to the cell indicating the old and new values. Generally, I don’t like error checking that stops the flow of entering data. Sometimes you have to stop everything because a piece of data must be right. However, it’s usually preferable to let the user keep entering data, then go back and fix what needs fixing.
I liken it to me typing a letter in word. I type three words per minute. I can type hundreds of characters per minute, but they stop counting words after my second mistake. That’s just stupid, in my opinion. I type my letters as fast as my fingers will go and I don’t give a hoot about mistakes. When I’m done, I go back and fix the mistakes and apply any formatting. That’s the way I want my data entry too. Let me put the data in, then go back and fix the errors. That’s not practical in every case, but in most cases it’s preferable to me.
What kind of error checking techniques do you build into your spreadsheets?