Spreadsheet disasters

Episode Summary

Executive Summary: The episode opens with a correction by the UK ONS that dramatically reversed G7 productivity figures due to a spreadsheet misalignment, then expands into a discussion with Matt Parker about how Excel errors routinely cause serious problems in public finance, banking, and scientific research. It concludes that spreadsheet tools are useful but fragile, and that human mistakes plus inadequate safeguards make errors inevitable unless systems are designed to catch them.

Main Topics: ONS productivity data correction (Priority: 5/5): The ONS initially reported the UK had the fastest G7 productivity growth in 2021, but later corrected the figures after discovering a column misalignment in Excel that made all country calculations wrong. How spreadsheet mistakes happen (Priority: 5/5): Matt Parker explains that Excel errors commonly arise from faulty cell references, missed ranges, and misaligned data, making spreadsheet-based calculations vulnerable to simple but consequential mistakes. Public sector spreadsheet failures (Priority: 4/5): Examples from Utah and West Baraboo show how local government budgeting and borrowing calculations can be badly distorted by spreadsheet mistakes, leading to large financial consequences. Private sector and banking risk errors (Priority: 4/5): JPMorgan Chase is cited as a high-cost case where spreadsheet-based risk calculations were flawed, contributing to major losses and showing that spreadsheet errors are not limited to government. Excel’s impact on scientific research (Priority: 4/5): Genetics researchers have had gene names auto-converted into dates by Excel, causing widespread publication errors and forcing the scientific community to adapt names rather than abandon the tool. Excel’s utility versus limitations (Priority: 5/5): The discussion ends by balancing Excel’s accessibility and usefulness for basic data work against its unsuitability for serious analysis without stronger safeguards and better error detection.

Key Arguments: The ONS G7 productivity error was not a small rounding issue but a full misalignment of output and hours-worked columns, making every calculation wrong. Spreadsheet mistakes are common, especially in public-sector and analytical work, because Excel makes it easy to build bespoke calculations that are hard to audit. Large private-sector losses can remain opaque until they become too costly to ignore, at which point organizations disclose only limited details. Excel can corrupt scientific data by auto-formatting gene names as dates, and this problem has persisted rather than disappeared. The issue is both human error and tool design: people make mistakes, and Excel does not reliably prevent or highlight them. A better environment for serious calculations is one that anticipates mistakes and actively flags suspicious results.

Data Points: UK productivity growth (initial ONS report): 22% - Originally published 2021 UK output per hour worked growth before correction UK productivity growth (corrected): -1.8% - ONS revised 2021 UK productivity from strong growth to a decline Canada productivity growth (initial ONS report): 18% - Originally reported 2021 figure before correction Canada productivity growth (corrected): -6% - Revised 2021 productivity figure Italy productivity growth (initial ONS report): 21% - Originally reported 2021 figure before correction Italy productivity growth (corrected): -1% - Revised 2021 productivity figure G7 productivity ranking correction for UK: From fastest growth to second slowest - ONS corrected the UK's relative position among G7 countries Utah State Office of Education budget error: $25 million - Budget miscalculation attributed to a faulty spreadsheet reference West Baraboo borrowing cost error: $400,000 - Cost estimate error caused by missing the bottom cell in a selected range JPMorgan Chase losses: Billions of dollars - Reported losses linked to flawed value-at-risk spreadsheet calculations Genetics papers with Excel gene-name errors (2016 study): Just over 19% - Published research found to contain autocorrect gene-name mistakes introduced by Excel Genetics papers with Excel gene-name errors (2020 follow-up): About 30% - Later review found the problem had increased Years reviewed in genetics study: 10 years - Researchers examined a decade of published genetics research

Pivotal Quotes: "It was a nightmare." — Janessa Brazil (clip within trailer): From the promotional trailer for Love, Janessa, describing the personal impact of stolen photos used in romance scams "We need to make sure we're doing these calculations and we're doing mathematics in an environment that knows we're going to make mistakes and is doing its utmost to point out when things might be going wrong." — Matt Parker: On the need for systems that catch human spreadsheet errors "This is not our fault." — Microsoft position quoted by Matt Parker: Microsoft’s response to Excel causing gene names to be auto-converted into dates

Implications: Spreadsheet errors can distort public policy, financial risk, and scientific records. Listeners are left with a warning: Excel is useful, but serious work needs stronger validation, better tools, and double-checking.