The ICAEW Blogs & Forums will no longer be updated with new posts. Your community announcements and articles will now be hosted on icaew.com under their respective community areas. This site and its contents will be closed and made available in an archive at the end of October.
Lots of errors happen in spreadsheets. Academic research on the subject suggests that over 90% of spreadsheets contain errors. The EUropean Spreadsheet Risk Interest Group (http://www.eusprig.org/ ) has an archive of articles addressing the issue and pages of 'horror stories' about errors that have occurred. The ICAEW has delivered three significant projects trying to address the issue, together with the issues of efficiency and productivity: The Twenty Principles for Good Spreadsheet Practice, the Spreadsheet Competency Framework and, most recently, the Financial Modelling Code.
In fact, so prevalent is the concept of spreadsheet error, that it has become a frequent refrain in news stories. Is this fair, or is it just lazy journalism? If an error occurs, and a spreadsheet was involved somewhere in the entire process, does that make the error a spreadsheet error? In the financial world it might be hard to find many processes and projects that don't involve a spreadsheet at some point.
In a recent news story on a major error in the specification of an Edinburgh hospital that required £16m to correct, the BBC used the headline: "Spreadsheet error led to Edinburgh hospital opening delay". What actually seems to have caused the error was the use of a generic, and incorrect, specification for ventilation air flow in the hospital's critical care room. The specification should have been to allow 10 changes of air per hour in these rooms but had been calculated based on 4 changes of air. Grant Thornton produced a report examining how the error occurred:
"A spreadsheet called the 'environmental matrix' and dated from 2012 contained the 'four air changes' error for critical care. The Grant Thornton report states: 'This looks to be, based on our review, human error in copying across the four-bedded room generic ventilation criteria into the critical care room detail'. None of the independent contractors involved in the matrix picked up on the oversight"
Is this really a spreadsheet error or just an error that happened to involve a spreadsheet? Is there anything about the way a spreadsheet works that caused this error, or would the use of a more suitable application than a spreadsheet have avoided this error? It could be argued that, if someone could use incorrect information and everyone else could fail to spot that error, the problem would have occurred whatever application had been used or not used.
On the other hand, perhaps there is something about spreadsheets that makes it more likely that people will fail to spot errors when they are used.
Douglas Adams identified the importance of the SEP (Somebody Else's Problem) field and the character, Ford Prefect, described it succinctly:
"An SEP is something we can't see, or don't see, or our brain doesn't let us see, because we think that it's somebody else's problem. That’s what SEP means. Somebody Else’s Problem. The brain just edits it out, it's like a blind spot."
Is there a chance that people are overwhelmed by spreadsheets, in particular their complexity and the lack of a rigid structure, and, however hard they try, their critical judgement is thwarted and invalid assumptions are made about the likelihood of the content being correct? Deficiencies in testing are a recurring theme in the EuSpRIG research.
Of course, many spreadsheet errors do have their roots in the nature of spreadsheets and the way in which they are used, and in the lack of appropriate training and experience of those using them. However, sometimes allegations of 'spreadsheet error' might just be a convenient diversion from a more human cause of error.
So, are spreadsheets being unfairly singled out, or do they really generate some sort of SEP field that increases the likelihood of error whenever they are used?
This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.
The initial cause here was a copy and paste error, which as others have commented, appears to be more likely in Excel than on paper. Copying and pasting calculations can be good thing: build and test once, use many times. Efficient and user-friendly! However, you must remember to check if the calculation is valid for the new area. Especially the inputs (here: number of air changes per hour) should be reviewed and updated as necessary, otherwise it can be a classic case of GIGO: garbage in, garbage out.
Hi Malcolm. Thanks for your comment. You make a very good point, and one that certainly bears repetition.
I don't see that the particular transposition error between documents could not have happened just as easily on paper. However the ease of using spreadsheets has tended to make calculations and demands for data far greater and more complex than would ever have been required in pen-and-paper days. This in turn, means they can be far more difficult to review.
Added to the fact that it appears people automatically trust something that seems to be generated by a computer more than something hand-written on a pad. This is all equally true of anything computer generated not just spreadsheets. So inevitable it happens, not just there, but everywhere and often