Raymond R. Panko
Although some spreadsheets are small "scratch pad" applications, many are large and complex, and many mission-critical decisions depend on spreadsheet analyses.
In recent years, we have learned a good deal about the errors that people make when they develop spreadsheets. In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists.
These error rates, although troubling, are in line with error rates in programming and other human cognitive domains. In programming, we have learned to follow strict development disciplines to eliminate most errors. Surveys of spreadsheet developers indicate that spreadsheet creation, in contrast, is informal, and that few organizations have comprehensive policies for spreadsheet development.
Although prescriptive articles have focused on such disciplines as modularization and having assumptions sections, these may be far less important than post-development testing.
This equation calculates the probability that a spreadsheet contains at least one error cell (E), given n cells and probability e that each cell contains an error.
Even for a small cell error rate and a modest number of cells, a spreadsheet is highly likely to contain errors.
1998, Journal of End User Computing, Volume 10, Number 2, Spring, pages 15-21