To Err is Human; To Detect and Prevent, Divine

By Mary Pat Campbell

Over a decade ago, I wrote my first article on spreadsheet errors. It was titled “To Err Is Human; To Correct, Divine”, and ran in the SOA’s technology section newsletter, detailing the commonality of spreadsheet errors – even among (or, I should say, especially among) numerical computing experts.

My years-long journey into trying to encourage better spreadsheet practices was spurred by the normal frustration one gets from inheriting other people’s spreadsheets. After learning how to clean up other actuaries’ spreadsheets, for actuarial use, I got the unenvied task of creating spreadsheets for non-actuaries to use… and had to learn about preventing the users from doing crazy stuff with the spreadsheets.

Join Mary Pat on December 11th for her latest webinar: Excel VBA to Document and Create Reports

However, many of the sources of errors remain. You can read the list of horror stories from the European Spreadsheet Risks Interest Group, and see:

• Hard-coded numbers used in calculations
• Hidden rows/columns or hidden values via formatting
• Formulas getting replaced by values
• Copy/paste disasters
• Insertion/deletion of rows/columns
• Formula incorrect (wrong formula used, wrong cells referenced)

This is not an exhaustive list, to be sure, but the point is that our spreadsheets often have errors, or potential errors, from faulty user input. It doesn’t help that Excel has additional error-tracing tools if we never use them. The most dangerous errors are often the ones we never looked for…until it’s too late.

Ultimately, one should expect errors, and actively seek them out as we develop our spreadsheets. A great deal of the source of errors in spreadsheets come from the very thing that make them so attractive for use: flexibility and apparent ease-of-use. Being able to make all sorts of changes on the fly, easily duplicating “code” (i.e. formula blocks via copying & pasting elsewhere), and getting immediate results sometimes prevents us from developing the discipline to be error-checking along the way.

It’s so much more fun to add new functionality, and not so fun to check prior steps before moving on. However, if we are to maintain our professional reputations for accurate calculations, we need to make sure we are preventing, detecting, and correcting errors throughout our spreadsheet use and creation.

In my on-demand webinar: Error Checking and Review of Actuarial Spreadsheets, I looked at all sorts of aspects of errors in Excel: the different error types that Excel will tell you about (and the ones that lurk silently), methods for detecting and tracing errors, methods for handling error occurrences, and some VBA.

in my upcoming webinar: Excel VBA to Document and Create Reports, December 11, 2019, I will look at ways you can use VBA to automatically generate documentation and reports from your spreadsheets.