Avoiding Spreadsheet Errors

Spreadsheets have become the indispensable tool for Finance professionals. Unfortunately, most spreadsheet users aren't formally trained as programmers -- they're self-taught, and picked spreadsheets up as they went along. Few spreadsheet users use the "test, test, and test again" approach to error checking that almost any trained programmer uses. So, not surprisingly, many spreadsheets have errors in them. Professor Ray Panko of the University of Hawaii has made a career out of studying these errors. Here's the abstract of his piece, titled "What We Know About Spreadsheet Errors":

Although spreadsheet programs are used for small "scratchpad" applications, they are also used to develop many large applications. 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 those 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 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 other innovations, especially cell-by-cell code inspection after the development phase.

According to this recent Wall Street Journal Interview of Panko, there are errors in about 1-2% of all formulas. According to other sources, there are likely errors in 20-40% of all spreadsheets. Panko's recommendation: budget about 1/4 of your time on error checking and trapping.

Ivars Peterson of MathTrek has a good post with some additional resources. One is a link to EuSpRIG a group that got its start in 1999. EuSpRIG is composed of individuals in business and academia. It promotes research on the nature and extent of risks associated with spreadsheets. Their site has got some excellent material on ways of preventing and detecting errors, techniques for limiting damage, and so on.

One excellent resource I found there is a piece titled "How do you know your spreadsheet is right? More than fifty Principles, Techniques and Practice of Spreadsheet Style". Some of it's a bit advanced for most spreadsheet users, but if you use spreadsheets on a regular basis, it's worth a read.