Why should you simplify?


When many people think of spreadsheet errors, they think of someone mistyping a number, typing a plus sign for a minus sign in a formula, or pointing to the wrong cell when entering a formula. While these errors do exist, there are many other types of errors in spreadsheet development. Panko and Halverson (Panko & Halverson, 1996) give a taxonomy of error types.

First, there are quantitative errors, in which the spreadsheet gives an incorrect result. The studies in Table 1 look only at quantitative errors. However there are also qualitative errors that may lead to quantitative errors later, during maintenance, what-if analysis, or other activities. Reason (Reason, 1990) uses the term "latent errors" for such problems. Teo and Tan (Teo & Tan, 1997) demonstrated in an experiment how one type of qualitative error led to quantitative errors during what-if analysis.

Panko and Halverson (Panko & Halverson, 1996), following Allwood (Allwood, 1984) also found it useful to distinguish between three types of quantitative errors. Mechanical errors are simple mistakes, such as mistyping a number or pointing to the wrong cell. Logic errors involve entering the wrong formula because of a mistake in reasoning. As noted earlier, logic error rates are higher than mechanical error rates. Logic errors also more difficult to detect and correct (Allwood, 1984). The most dangerous type of error is the omission error, in which something is left out. Omission errors appear to be extremely difficult to detect (Allwood, 1984; Bagnara, Stablum, Rizzo, Fontana, & Ruo, 1987; Woods, 1984).

When Panko and Halverson (Panko & Halverson, 1997) analyzed the types of errors that subjects made while developing a spreadsheet, they found that all three forms of errors were common. Later, Panko and Sprague (Panko & Sprague, 1998) found the same broad pattern of errors. Panko and Halverson compared different types of errors to multiple lethal poisons. Even if all errors of the other two types were eliminated, each type of error alone would have produced an unacceptable number of incorrect spreadsheets.

Techniques to reduce errors may be better at finding some types of errors than others, so it is important to begin developing corpuses (collections) of errors to learn how frequent different types of errors really are.

Credits

Raymond R. Panko
University of Hawai’i
College of Business Administration
2404 Maile Way
Honolulu, HI 96822

Jeremy Olshan is the editor of MarketWatch. 


Providing a collective bargaining software that helps you see employee costs today and project future payroll costs. Model what-if scenarios, experiment, and understand the budget impact of each decision.