Raymond Panko, a professor at the University of Hawaii, is the leading expert on spreadsheet errors. Who makes them, why they happen, what rate they’re seen in the average spreadsheet, you name it.
He wrote a paper on the subject called What We Know About Spreadsheet Errors first published in the Journal of End User Computing.
Here is an excerpt:
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 real issue is how many errors there are, not whether an error exists.
Most studies looked at errors at the end of the development stage, when subjects said that they were finished developing their spreadsheets. Cell Error Rates (CER) across these studies were similar, despite the fact that subjects ranged from novices to highly experienced spreadsheet developers. One study even compared undergraduate business students, with little spreadsheet developing experience, and MBA students with more than 250 hours of spreadsheet development experience. Their Cell Error Rates were very similar.
Even when a task was selected to be very simple and almost completely free of domain knowledge requirements, the Cell Error Rate was about 2%.
Respondents were keenly aware of the dangers of error and spent considerable time working to reduce errors, including the creation of cross-footings, doing spot checks of formulas with calculators, and having others look at output. However only 1 of the 11 interviewees said that they had done a complete code inspection of a spreadsheet; and in that case it was a spreadsheet given to that person by someone else!
Later, Hendry and Green did a similar study, this time with 10 spreadsheet developers. They added a phase in which the subject walked through a spreadsheet with an author. They found that subjects often had a difficult time explaining parts of the spreadsheet that they had themselves built.
They also found that some of the spreadsheet developers had run into serious problems trying to make parts of the spreadsheet analysis fit the row-and-column layout of spreadsheet program.
We are overconfident about the accuracy of spreadsheets, despite the large amount of information to the contrary. One study gave three spreadsheet development tasks to nine highly experienced spreadsheet developers, all made at least one error, not all of which were caught and corrected. In fact, 63% of the final spreadsheets overall had errors. Yet when asked about their confidence in the correctness of their spreadsheets, their median score was “very confident.”
Overconfidence is corrosive because it tends to blind people to the need for taking steps to reduce risks.
Whatever specific techniques of improvement are used, one broad policy must be the shielding of spreadsheeters who err from punishment because a climate of blaming will prevent developers from acknowledging errors. Quite simply, although the error rates seen in research studies are appalling, they are also in line with the normal accuracy limits of human information processing. We cannot punish people for normal human failings.
To read more, including the author’s suggestions for mitigation, please review the article >>>
What We Know About Spreadsheet Errors
