Stephen G. Powell, Kenneth R. Baker, & Barry Lawson
Spreadsheets are used in almost all businesses, for applications ranging from the mundane to the mission-critical. Errors in the data, formulas, or manipulation of spreadsheets could be costly, even devastating.
The received wisdom is that about 5% of all formulas in spreadsheets contain errors, and this rate is consistent across spreadsheets. However, this estimate is based on five studies, some of which are quite informal, and a total of only 43 spreadsheets.
Our research was designed to deepen our understanding of spreadsheet errors. Specifically, we address three questions about errors in operational spreadsheets: what is the average cell error rate, how does it differ among spreadsheets, and what types of errors are most prevalent?
We created a spreadsheet auditing protocol and applied it to 50 diverse operational spreadsheets. We found errors in 0.9% to 1.8% of all formula cells, depending on how errors are defined. We also found that the error rate differed widely from spreadsheet to spreadsheet.
46% of our sample spreadsheets had error rates below 2%; 70% had error rates below 5%. However, several spreadsheets had error rates above 10%; in fact one had a cell error rate of 28%.
The average cell error rate (CER) in this sample is 1.79%.
Only 3 out of 50 spreadsheets had no detected errors. That is, 94% of the spreadsheets contained errors.
2009, Journal of Organizational and End User Computing, Volume 21, Number 3, July-September, pages 24-36