[Cover Image Being Wrong, Schulz, Kathryn. A great book.]
The errors of others
In these hot summer months it’s always nice to look at others’ mistakes (never our own) for some light relief.
Errors happen everywhere, and the human/technology interface is no exception.
There’s research going back 30 years about Excel-induced errors and it never reads well
“90% of spreadsheets have errors!” – pick your year
but one suspects Excel is no worse than any other interface. It just gets traduced for being so popular.
Nevertheless, it is worth looking at the types of error that crop up with Excel.
Here’s a few of the more news-worthy cases.
Rounding (“crash rounding”? 🙂
-
- Rounding: catastrophic metric vs imperial conversions killed one Mars probe.
Formatting “bugs” (sorry, too easy)
-
- Number Formatting: MI5 bugged the wrong phones because of numbers ending in 000;
-
- Date Formatting: 27 human genes were renamed because Excel kept misreading them as dates. “Membrane Associated Ring-CH-Type Finger 1” used to be known as MARCH1. Well you can guess what happened. Studies estimate a fifth of genetic data referenced in papers had Excel errors.*
-
- Reformatting: Barclays hid rows for assets it was not interested in purchasing as part of Lehman Brothers’ bankruptcy. When reformatted as PDF for the court those rows reappeared, and all those unwanted assets were now legally committed to, and subsequently purchased.
Software Limitations vs Human Usage
-
- Row Count | Old Format: Public Health England used Excel as its database for coronavirus reporting. Reports were originally gathered in CSVs (no problem there – small, fast) but later converted to XLS limited the rows collected to 65k, and thousands of people weren’t contact traced in time.**
Rows
-
- Misalignments: TransAlta lost $24 million when misaligned rows meant bids were aligned with the wrong contracts.
-
- Misalignments | Formula Range: a famous economics paper (Reinhart–Rogoff) had formulae referencing incomplete ranges. The result effected conclusions about the optimum level of debt a government can carry.
Human Error
-
- Fidelity Magellan fund: missing minus sign made a $1.3bn loss appear as a gain (and triggered a dividend distribution);
-
- Fannie Mae: $1.1 billion error at as accounting standards changed;
-
- Eastman Kodak: too many zeros resulted in an $11 million overstatement;
-
- JP Morgan: $6 billion trading loss versus the Whale when a copy paste understated true risk.
Does it all mean anything?
Honestly, I don’t think so. The problem isn’t Excel – it’s the controls around it.
As we’ve covered elsewhere, the Bank of England thinks so too –
Excel is awesome, we’ll all never quit it, but we do need to be careful about how and what we are using it for to avoid becoming another Excel casualty.
About Circadian
Transform office procedures into services. Fund ranking, Model Portfolios, Client Docs, Client Comms. These are just some of the ways we build your business.
Footnotes
[*] Be careful of numbers around the 40,000 range!
http://www.eusprig.org/horror-stories.htm
https://www.wired.co.uk/article/spreadsheet-excel-errors
[**] “But you wouldn’t use XLS. Nobody would start with that.” – Prof Jon Crowcroft from the University of Cambridge. “It’s a format from a different millennium.”
