For the second time in the last three months, I had a grateful client report back to me that a new report commissioned out of my software product uncovered errors in their former spreadsheet-based billing system. In this case, they would have under-billed a client by several hundred dollars for the quarter. Considering that this spreadsheet had been in use for years and copied to other spreadsheets for billing other customers, one wonders what the total cost was.
Spreadsheets are like high blood pressure: the silent killer of businesses. Of course, this means every business except yours and mine. It’s all those other users who have error-riddled spreadsheets with mistyped formulas and errant cell references. I have every confidence that yours and mine fall into the ten percent of all spreadsheets that contain zero errors.
90% of All Spreadsheets Have Errors
Raymond Panko has been researching and documenting spreadsheet errors since 1997. In 2009, he conducted seven field audits of operational spreadsheets and found that an astonishing 88% of them contained errors. At first blush, this is hard to swallow. But what if I were to tell you that one in every twenty cells with a formula is flawed? That doesn’t seem so hard to believe. If you then allow that the typical operational spreadsheet averages about a hundred cells with formulas, then it suddenly becomes quite plausible.
Another field audit of fifty operational spreadsheets by Stephen Powell at Dartmouth College found that only three of them (94% percent) were error-free. In this study, six main types of errors were reported:
- Hard-coded numbers in formulas where cell references were intended
- Formulas that contain incorrect cell references
- Logic errors caused by using the wrong function or incorrectly implementing the function
- Copy/paste errors
- Inaccurate calculations due to cells with omitted data
- Data input error
These sorts of errors are known to cost businesses billions of dollars every year. The European Spreadsheet Risks Interest Group published a list of 89 “horror stories” encountered in their own evaluations.
But I’m sure yours and mine are perfectly fine.
What’s the Point?
Databases. Most operational spreadsheets I’ve come across are a case of trying to hammer a nail with a banana. They are situations that are best suited by a bona fide database back end and user interface. The fundamental problem with a spreadsheet is that the data and business logic are not separated from the user. One inadvertent keystroke can wipe out a carefully crafted formula written five years ago by a statistical guru no longer employed by the company. Fault tolerance is another issue, as is data type checking and many other standard features of a properly designed databases that will protect both the data and the formulas.
I’m particularly sensitive to this topic after responding to Gary Mintchell’s recent blog post, “You should all learn SQL.” In it, he noted the proliferation of databases and subsequent need for more non-IT personnel to learn SQL. As this discussion points out, however, maybe the proliferation is not quite fast enough.
I’d love to hear your thoughts and, particularly, any spreadsheet horror stories you’d like to share.