Source-linked AI summary
Spreadsheet Errors: What We Know. What We Think We Can Do
Raymond R. Panko
TL;DR
Recent field audits found errors in at least 86% of spreadsheets, and spreadsheets can support important organizational work. The paper reviews evidence about spreadsheet errors and proposed ways to reduce them, finding group cell-by-cell inspection the only technique demonstrated to be fairly effective. More experiments are needed to establish whether other approaches reduce errors.
Problem
Recent field audits found errors in at least 86% of spreadsheets, while many spreadsheets are rated important or very important to their organizations.
Source: Introduction · p. 1, What We Know · p. 2Method
The paper synthesizes field-audit and laboratory evidence and assesses proposed spreadsheet error-reduction practices and testing approaches.
Source: What We Know · p. 2, What We Think We Can Do · pp. 5–6Results
About 80% of errors were caught by group cell-by-cell code inspection, the only approach reported as experimentally tested and fairly effective.
Source: Conclusion · p. 6Takeaways & Limitations
Further experimental research is needed to determine which other error-reduction approaches are effective.
Source: Conclusion · p. 7Proposed good-development practices have not been experimentally shown to reduce errors and may make formulas harder to inspect.
Source: What We Think We Can Do · p. 5
Abstract
from arXiv · showhide
Fifteen years of research studies have concluded unanimously that spreadsheet errors are both common and non-trivial. Now we must seek ways to reduce spreadsheet errors. Several approaches have been suggested, some of which are promising and others, while appealing because they are easy to do, are not likely to be effective. To date, only one technique, cell-by-cell code inspection, has been demonstrated to be effective. We need to conduct further research to determine the degree to which other techniques can reduce spreadsheet errors.
Introduction
Spreadsheets are widely used, often large and complex, and frequently support important organizational work. Because spreadsheet errors can distort bottom-line values and human cognition has accuracy limits, errors pose a serious risk.
Introduction
- Some surveyed spreadsheets were large and complex: one audit covered 10,000 cells, and another survey’s average selected model had 6,000 cells.Spreadsheets also use many sophisticated functions.Source: Introduction · p. 1
- Forty-six percent of 402 non-trivial spreadsheets in one organization were rated important or very important, and 59% were used at least monthly.In a separate survey, 42% of developers named their chief executive officer as the highest-level user of their spreadsheet’s data.Source: Introduction · p. 1
- Unless development errors are near one per 10,000 cells, most large spreadsheets are likely to contain errors that produce incorrect bottom-line values.Errors can have serious consequences when spreadsheets support organizational work.Source: Introduction · p. 1
- Human error research attributes mistakes to fundamental limits in cognition, where accuracy is traded for speed; spreadsheet accuracy therefore requires overcoming or reducing those limits.The passage contrasts spreadsheet accuracy requirements with error rates tolerated in hunting and most computer applications.Source: Introduction · pp. 1–2
What We Know
Field audits and laboratory experiments show that spreadsheet errors are widespread, while spreadsheet error reduction remains difficult. Cell-by-cell group code inspection is the only approach described as experimentally tested and fairly effective; other proposed practices require more evidence.
Data from Field Studies
- Recent field audits generally found errors in at least 86% of spreadsheets, compared with 24% across seven audits of 367 spreadsheets overall.Most older audits used methods unlikely to catch most errors; audits generally reported substantive errors rather than all errors.Source: Table 1 · p. 2, What We Know · p. 2
Data from Laboratory Experiments
- About 51% of laboratory spreadsheets contained errors, and all experience groups made errors, with no significant rate differences across the compared groups.The roughly 1,000 subjects’ tasks had known solutions; spreadsheets were mostly 25–50 cells, and whole-spreadsheet cell error rates were at least 1%–2%.Source: Table 2 · p. 2, What We Know · pp. 2–3
Error Types and Life Cycle
- Quantitative errors include mechanical slips, incorrect algorithms or formulas, and omissions; qualitative errors may create later problems without immediately changing bottom-line values.Errors can arise during any lifecycle stage, including ongoing use, when users enter data or hardwire formula cells.Source: What We Know · pp. 3–4
Cell Protection and Re-Keying Data
- Cell protection can prevent hardwiring, while duplicate input sections can expose data-entry differences through verification.Cell protection is used in half or fewer of spreadsheets; verification relies on data-entry errors being unlikely to recur identically.Source: What We Think We Can Do · p. 5
Examining Results and Good Development Practices
- Reasonableness checks are inexpensive but unreliable as a stopping point, while good-development practices have not been proven effective experimentally and may impede code inspection.Separating inputs from logic may make formulas harder to read; the paper calls for experimentation before effectiveness claims.Source: What We Think We Can Do · pp. 5–6
Error Discovery Software
- Spreadsheet error-discovery software remains untested, and it is unclear whether graphical cell-connection displays would make errors found in experiments and audits more visible.A second proposed form highlights specific cells that may contain errors, but its ability to catch observed error types is also unknown.Source: What We Think We Can Do · p. 6
Code Inspection
- Execution testing is difficult when known answers are unavailable and requires carefully chosen extreme and out-of-bounds cases, which spreadsheet testing often omits.Software development examples show that even extensive testing can leave errors, and the paper cautions against expecting much from methods short of massive testing.Source: What We Think We Can Do · pp. 5–6
- Group cell-by-cell code inspection is the only method reported as experimentally tested and fairly effective, catching about 80% of errors, but it is expensive and exhausting.Individual inspectors find only half or fewer of errors; firms prioritizing accuracy may need teams of three or more.Source: What We Think We Can Do · p. 6
Conclusion
Spreadsheet errors are common and nontrivial, and ordinary care does not eliminate them. Group cell-by-cell inspection is the only approach demonstrated to be effective, yet further experimental research is needed to assess other methods.
Conclusion
- Spreadsheet error rates are comparable to those in other human cognitive activities and reflect cognitive limitations, not mere sloppiness.Ordinary care does not eliminate errors or reduce them to acceptable levels.Source: Conclusion · p. 6
- About 80% of spreadsheet errors are caught by group cell-by-cell code inspection, the only approach experimentally found fairly effective to date.The process is expensive and exhausting, and individual inspectors find only half or fewer of all errors.Source: Conclusion · p. 6
- An experimental research program is needed to determine which other error-reduction approaches are effective.The persistence of errors even after cell-by-cell inspection warrants caution about expecting too much from other approaches.Source: Conclusion · p. 7