Spreadsheets

S

Spreadsheets are the bedrock of the modern enterprise, they’re ubiquitous, from small family business’ to large multi-nationals, and you’d be surprised by the number of critical activities that run off them.

Pound-for-pound, Microsoft excel is the most valuable piece of software on the planet.

But are really that good?

The answer depends on what you mean by ‘good’?

If you need something flexible and editable by a user, which is universal enough to ensure everyone in the organization can view the data — then yes, spreadsheets are good.

But if you need something that powers a critical business process, where scalability, stability and accuracy are critical — then no, spreadsheets are evil!

But why would a spreadsheet not be accurate?

Well … buried deep within Excel’s code are a bunch of auto-corrects which often corrupt data. An auto-correct is a programs attempt to correct a human-error, and generally speaking it works, but occasionally it takes perfectly good data and makes it bad.

When you type in ‘MARCH1’ into an cell on your spreadsheet, Excel automatically thinks you’re entering a date, and converts that entry into ‘1-Mar.’ Normally, this would be desirable, but if you’re a geneticist, ‘MARCH1’ could be short for “Membrane Associated Ring-CH-Type Finger 1” — which isn’t a date.

The problem is so perverse, that HUGO Gene Nomenclature Committee, decided to rename these genes, rather than fixing Excel:

This week, the HGNC published new guidelines for gene naming, including for “symbols that affect data handling and retrieval.” From now on, they say, human genes and the proteins they expressed will be named with one eye on Excel’s auto-formatting. That means the symbol MARCH1 has now become MARCHF1, while SEPT1 has become SEPTIN1, and so on. A record of old symbols and names will be stored by HGNC to avoid confusion in the future.

If the worlds brightest scientist have been defeated by Excel, what chance does Bob from accounting have?

But the reason for this post though, is the offense I took from a claim that NHS in the UK was ‘incompetent’ because it used spreadsheets to track contact tracing. The mis-step resulted in a failure to inform nearly 50,000 people to self-isolate, which might be partially responsible for the recent rise in cases.

For sure, it was a terrible mistake.

But incompetence is a strong word, it means “inability to do something successfully”, and generally refers to a innate inability as opposed to something environmental. You fire people for incompetence, recognizing that an average individual under the same conditions would have performed adequately.

But people resort to spreadsheets, not out of choice — but financial necessity. Most organizations equip their desktops and laptops with a Microsoft Office (which includes Excel), and hence distributing .xls or .xlsx files is a cheap way for any project to distribute data — they don’t have to worry about the hosting anything, or installing anything on people’s laptops.

The NHS was strangled out of cash, and yes, they could have spend a couple hundred thousand dollars to have a system that did all this tabulation in a accurate, stable and scalable way. But someone probably decided that buying extra ICU beds was a better use of that money. Can you blame them?

The Excel spreadsheet (up until then) was working perfectly, it had a few drawbacks, but spending money on something that isn’t broken, is a luxury few Governments can afford.

Nobody anticipated a COVID like epidemic, and hence nobody was preparing IT systems to handle this load of daily patient data — I’m guessing the UK isn’t reporting 17,000 daily cases of any other illness. The software had no a reason to exceed its limits, so the current limits were deemed adequate (and reasonably so).

Hence, the reason the software missed out these 50,000 people, is the same reason we don’t have enough ICU beds. We just weren’t prepared for this scale.

Yes, they should have used a proper database, with a proper software running. At the very least, they should have reported an error when the spreadsheet was filled over. But those things cost money — and depending on who you ask, the NHS has experienced a decade of under-funding, with three quarters of NHS workers saying there isn’t enough staff in their ward, and 49% reporting they couldn’t take breaks because of their workload.

Do you really think they had couple hundred thousand lying around to implement the software?

The NHS used spreadsheets not because they were incompetent, but because they were underfunded. Incompetence is a devastating insult to folks who are already trying their best with the limited resources they have.

Addendum

For those asking why a simple error message wasn’t present when the spreadsheet overfilled — the answer is also economic. Delivering a working IT solution is usually cheap and easy, but making that system good requires a multitudes of complexities, from scalability, disaster recoverability, archival, performance, running cost…and the list goes on.

Merely testing your system to figure out the breaking points requires time and effort — all of which eventually translates to cost. The ‘error’ wouldn’t have been discovered, unless someone predicted a epidemic with more than 10,000 patients a day — something no one was expecting a decade ago (or even 18 months ago).

Sure it could have been done — but who was going to spend money to test for a situation that no one had seen before, or even realistically expected? — especially when budgets were tight?

No one!

1 comment

Astound us with your intelligence

  • One could just have to right-click, and format the corresponding input cell as “text” in Excel to prevent the “auto correct”, and optionally to make some things easier, can turn off auto correct and spell-checking in Word/Excel options menu. The so called “scientists” should learn how to use Excel on the first hand rather than blaming it and resorting to use a dumb-sounding acronyms. Stupidity has no fix.