Updated: Oct 7, 2020
There are no "small" mistakes in spreadsheets. Any mistake can have significant consequences.
TransAlta - $24 Million - Cut and paste error - paid too much for hedging contracts.
Fidelity - $1.3 Billion - Missing negative sign turned a capital loss into a gain.
University of Toledo - $2.4 Million - Typo in formula led to overestimate of enrollment and revenue
Emerson - $3.7 Million - Cost formula excluded one cell in the spreadsheet
Why do errors occur in spreadsheets?
Developing a spreadsheet is a complex task.
"Small" mistakes are hard to detect.
We make more mistakes than we realize - especially true for experienced developers.
Failure to detect all errors is more the rule than the exception.
Sometimes a spreadsheet will let you know there is a mistake by giving you an error message:
#DIV/0! - You are trying to divide by zero.
#N/A - Your formula points to data that does not exist.
#NAME? - The formula does not understand the text in your formula.
#NUM! - You have the wrong type of number for the calculation you want to do.
#REF! - Your formula has invalid data.
#VALUE! - Your formula is using the wrong operand or function.
Your chances of finding errors on your own are only slightly better than 50%. In his synopsis of Audits of Operational Spreadsheets, Ray Panko states that "Experiments in which participants attempted to identify errors in a spreadsheet found that they only caught 60% of the errors"!
When you find the error is just as important as actually finding the mistake. The later you detect the mistake the more expensive it is to fix it.
In his article "Defect Prevention: Reducing Costs and Enhancing Quality" Mukesh Soni says: "The Systems Sciences Institute at IBM has reported that the cost to fix an error found after product release was four to five times as much as one uncovered during design, and up to 100 times more than one identified in the maintenance phase (Figure 1)."
Figure 1: Relative Costs to Fix Software Defects (Source: IBM Systems Sciences Institute)
What can you do to reduce the number of errors in your spreadsheet?
Accept that mistakes will occur, so test at every stage of development - design, implementation, testing, and maintenance.
Implement some form of the review process. Once a section of the spreadsheet is completed, have it reviewed by another analyst. Better yet, give the spreadsheet to a user and let them have at it. In my experience, it is the best way to uncover unexpected errors.
Log your process as you develop the spreadsheet. You never know when you will need to go back to an older version to fix a problem. If you do, you'll want to know what you were thinking at that time.
What process do you use to avoid or correct spreadsheet errors? Do you have any horror stories of your own you'd like to share? Please leave a comment and share it with others.