Authors
Warren M. Anderson
Abstract
Research has confirmed that spreadsheet errors are very common, with spreadsheet error rates being comparable to those found in traditional software development. Whilst other methods exist, such as two or more people checking the spreadsheet manually, no single technique has been found that is able to detect the majority of spreadsheet errors.
However, in 2001 a promising research project was conducted by Nixon and O'Hara which examined five spreadsheet audit tools and concluded that they were a useful aid for helping spreadsheet developers and auditors to locate certain kinds of spreadsheet errors.
To evaluate how well audit tools performed, both in terms of personnel and software, a sample spreadsheet, containing fifteen errors, was used to test the ability of sixty-five participants and eleven spreadsheet audit tools to detect the same errors. The results were then compared to analyse whether the two groups found the same or different errors.
The results, whilst confirming the research hypothesis that audit tools and participants would locate different types of errors, further showed that the audit tools did not perform as well as the participants both in terms of the number of errors detected and the reasons for them.
While these results supported previously held views that audit tools were unlikely to find the greatest number of the errors, they also confirmed the view of the 2001 study that spreadsheet audit tools were useful in helping to locate specific kinds of spreadsheet errors not detected by human participants. That finding leads to the conclusion that both spreadsheet tools and technically competent users are needed to ensure potentially costly errors are detected and corrected.
Sample
Some of the techniques used by the audit tools to assist in the auditing process may actually make the task more complicated.
In this example, a tool struggled with the use of merged cells in the spreadsheet and produced a map that showed that some cells contained formulas even though they were empty.
Publication
2004, Massey University
Full article
A comparison of automated and manual spreadsheet error detection
