The news that more than 15,000 positive UK Covid cases became lost in a “technical glitch”, apparently due to Public Health England using old Excel software (which only has around 65,000 rows per sheet), does not really come as a surprise. I am possibly slightly surprised that pre-2007 versions of Excel are still being used, but a spreadsheet error? This is, unfortunately, very normal.
Another modelling error hit the press a few weeks ago because a formula in a spreadsheet had apparently not been copied across which caused a delay in a hospital opening. Modelling errors also supposedly caused the £50 million additional cost of rebidding the West Coast Main Line franchise. There are thousands more tales of modelling “bugs” out there.
What is the problem here?
It is because Excel is widely used, but not widely understood.
Take these quotes, all said to me many times:
- “Young people understand all about computers, they learn Excel at school and therefore are able to model”
- “It is easy to learn to play around with Excel”
- “We employ qualified accountants; therefore, they know all about financial modelling”
- “The people we employ are intelligent, they can pick Excel up as they go along and do not need formal training”
It is because of these dangerous assumptions that insufficient attention is paid to proper training, thereby opening up companies and governments to unnecessary risks and inefficiencies.
For example:
- How many people are even aware of the calculation method of their spreadsheets, let alone the implications of different settings?
- How many people even know about the simple methods of checking for empty cells, inputs or whether a formula is copied across?
- How many people include rigorous error checking in their spreadsheets? Judging by the fact that my blog with the fewest “likes” was one about checklists, I think very few. The trouble is that checks are not very interesting. But they are crucial.
But spare a thought for the poor modellers. Excel contains so many pitfalls and potential problems, but also many time-saving tricks. Excel users need to know about these, or their spreadsheets will undoubtedly take far too long to build and contain errors, becoming dangerous tools. Are modellers really expected to find out about Excel’s nuances on a hit and miss basis through Google?
Proper training reduces risk and saves time. Why would anyone ever try and bypass this necessity?