Questions About Spreadsheet Troubleshooting
When discussing errors in spreadsheets, the various ways of dealing with them, the cost of doing so and the risk of not doing so, there are several overarching questions we need to answer:
- How widely are spreadsheets used?
- How often do errors in spreadsheets occur?
- What are the potential risks associated with undiscovered spreadsheet errors?
- How long does it take to discover spreadsheet errors without specialized tooling?
Here is what information is available to inform our answers:
How widely are spreadsheets used?
According to Scoop: Google's G Suite cracks 2 billion users ⧉ Google Docs had reached over 2 Billion active users by the end of 2019. By the word of Javier Soltero in charge of Google Suite:
That's a staggering number ... These products have incredible reach. Changing the way people work is something we are uniquely positioned to do.
It is unclear what portion of these users were using Google Sheets in particular. We can expect that by now the number of total Google Docs user has increased.
According to Microsoft Excel Becomes a Proper Programming Language ⧉ :
In 2017 Microsoft CEO Satya Nadella was calling Excel Microsoft’s most important consumer product, pointing out that it had over 750 million users.
How often do errors in spreadsheets occur?
According to Cassotis Consulting 88% of the Excel spreadsheets have errors ⧉ . According to the article commenting on different sources for that figure:
Considering the complexity of Excel spreadsheets and the lack of mastery by many users, we have come to the title of this post: more than 80% of the Excel spreadsheets have errors. Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas.
What are the potential risks associated with undiscovered spreadsheet errors?
$6.2B are the losses from just one incorrect spreadsheet - London Whale incident. We have compiled a list of various spreadsheet mishaps for your horrified eyes' viewing pleasure.
How long does it take to discover spreadsheet errors without specialized tooling?
According to The Art of Spreadsheet - web archive link ⧉ by John F. Raffensperger it takes about a minute per formula:
The time required to check each formula depends on the readability of the spreadsheet, but bet on more than a minute per formula.
The author offers a detailed step by step approach to checking spreadsheets that involves much more steps than simply checking formulas. Thus, we can assume that the value quoted is for the entire process and it is averaged per the amount of formulas on a spreadsheet. By this logic, a spreadsheet with more than 60 formulas would take more than an hour to verify by hand.
Naturally, the level of effort to check a spreadsheet would depend on the importance of the data it contains. The level of effort required also depends on the complexity. By the words of John F. Raffensperger ⧉ :
How much effort should you spend on checking a spreadsheet? The answer depends on the amount of money involved. Of course, you should spend more time auditing a spreadsheet that depends on more money.
It is safe to say that if the results of the calculations are used to drive business decisions, the results need to be correct 100%.
Now that the basic questions related to spreadsheet mistakes are know, go back and check how you can use tools to quickly discover and fix the errors .