Pasting error

In June 2003 a pasting error was discovered before it was too late. The error came to light in a university examiners meeting: the spreadsheet containing the marks for a small joint degree (3 or 4 students) was found to be wrong. The numbers just didn't make sense, as the average marks were inconsistent with the range of individual marks. The numbers were recalculated by hand before the end of the examiners meeting, and no harm was done, let alone $24m worth (see the note on TransAlta Corporation).

The problem was caused (like so many others) by copying and pasting: the formulae were correct in the first row, but not in the later rows. History doesn't relate, but presumably it was a question of relative versus absolute references.

The spreadsheet had actually been checked before the meeting by the Chair of one of the departments involved. Unfortunately, the check had been confined to the first row, which was indeed correct.

There are several morals to this story.

• Be very afraid when copying and pasting
• Don't rely on checking the formulae, look at overall reasonableness too
• If you're checking a representative sample of formulae, remember that the first row (or first column) probably isn't representative.

Resources

Ray Panko

Professor Panko, who is at the University of Hawaii, has done a great deal of research into spreadsheet errors. His page at http://panko.cba.hawaii.edu/ssr/ has many useful resources, including links to many of his papers.

European Spreadsheet Risks Interest Group

This special interest group provides a forum for research on spreadsheets. Its website is at http://www.eusprig.org and includes an excellent collection of spreadsheet horror stories. EuSpRIG organises an annual conference on spreadsheet risks.