Type “spreadsheet error” into Google and a litany of woes will unfurl. Spreadsheet error is serious stuff: so serious that it’s a research field in its own right, and even has an international organisation devoted to it. The leading researcher in the field, a fellow called Raymond Panko, concludes:

All in all, the research done to date in spreadsheet development presents a very disturbing picture. Every study that has attempted to measure errors, without exception, has found them at rates that would be unacceptable in any organization. … With such high cell error rates, most large spreadsheets will have multiple errors, and even relatively small “scratch pad” spreadsheets will have a significant probability of error.

Surely any serious errors would be picked up before they did lasting damage? That’s what everybody hopes, but unfortunately the answer is: sometimes. And sometimes not. At the height of the banking crisis, for instance, Barclays Capital accidentally bought 179 trading contracts they didn’t want from the collapsed Lehman Brothers, all because of a spreadsheet formatting error. As one study of spreadsheet errors put it:

We draw two fundamental conclusions… First, it is clear that spreadsheet errors sometimes lead to major losses and/or bad decisions in practice. Indeed, we heard about managers losing their jobs because of inadequate spreadsheet quality control. Second, many senior decision makers whose organizations produce erroneous spreadsheets do not report serious losses or bad decisions stemming from those flawed spreadsheets. Hence, it seems in no way inevitable that errors in spreadsheets that inform decisions automatically lead to bad decisions.

But then again, it is in no way inevitable that they won’t. (Unless spreadsheets totally lack influence in your organisation, in which case why create them?)

Of course spreadsheets have their place. You use them all the time, and so do I, because they’re easy, flexible, and great for doing things “on the fly”. But a useful spreadsheet also has a tendency to grow like Topsy, until you start to worry that you’re not entirely sure exactly how it works, keep finding mistakes in it, notice “odd” results that don’t reconcile, and find that you’re spending inordinate amounts of time on it with relatively little to show for your efforts.

Or, to get down to the nitty gritty, you might remember times when:

• a formula was “temporarily” changed to a number, but not changed back again;
• a formula was changed, but the change was not copied across to other cells;
• an error was made when typing in data from a different source;
• cells were linked to an external table, but some of the links were misaligned.

We’ve all been there.

Once a spreadsheet becomes complex, or needs to be flooded with data from a database, or needs to run very similar calculations over and over again with different numbers, then it probably shouldn’t be a spreadsheet any more. NHS planning spreadsheets will typically tick all those boxes.