Reducing spreadsheet errors by suggestion?
Number-oriented people love their spreadsheets. Every once in a while I run into someone who has created a prized Excel document encrusted with VBA macros, filters and formulas that make String Theory look simple. Usually these monsters started life as a quick-and-dirty “what if” then grew over several years like fungus.
As I’ve written before, testing of these beasts is usually minimal yet the calculations they produce are trusted as gospel. Spreadsheet errors are estimated to cause billions in losses each year, yet the issue remains a blind spot for most organizations.
Oregon State University has announced a unusual tool they’re calling “GoalDebug” that attempts to help reduce spreadsheet errors. I say “unusual” because the software isn’t an auditing tool to verify spreadsheet accuracy in any definitive way, but rather what sounds like an expert-system driven suggestion system. According to the announcement, the tool identifies ways humans commonly make mistakes then “gives end users a chance to explore, apply, refine, or reject suggested changes”. From the announcement:
For instance, if someone sees a figure in a spreadsheet that seems suspicious or is clearly incorrect, they can plug in the correct number, and the OSU system can suggest several programming mistakes that might have created the error – which the user can then sort through and use to identify the problem.
I’m sure the system bears no resemblance but that description reminds me of the “Clippy” assistant for MS Office. As an end-user on-the-job training tool such a tool might have value, but should software regularly used for critical financial analysis be debugged with such a loose approach? Compare the functionality to other statements in the announcement:
“…it has been observed that up to 90 percent of the spreadsheets being used have non-trivial errors in them.”
“…the costs or financial misrepresentations are far more serious, and companies have lost millions or billions of dollar”
“There are dozens of places an error can be made… A person can click their mouse in the wrong spot, a simple mechanical error. They could use a plus instead of a minus, add a row at the end of a data area instead of in the middle, and get a completely different result.”
What other form of software is widely used and trusted yet results in errors 90% of the time, costs millions or billions and can be broken dozens of trivial ways? If spreadsheets are this bad, maybe it’s time to rethink using spreadsheets at all.
Related posts: