Spreadsheets - accurate or archaic?

Written by Jill Day of the UK distributors of Forecast 5, Kipps Business Reporting.

What to do when details of 16,000 Covid-positive cases disappear from Test and Trace spreadsheets?

‘To err is human but to really foul things up requires a computer’ is attributed to Paul Ehrlich, the American scientist: Public Health England proved it to be true of Excel when details of 16,000 Covid-positive cases disappeared from its Test and Trace spreadsheets in early October last year, resulting in 1,500 unnecessary deaths. Someone had used the wrong Excel file format, XLS rather than the more recent XLSX. And XLS spreadsheets have 2 rows to the power of 16, about 64,000. This meant that during some automated process, cases had vanished off the bottom of the spreadsheet, and nobody had noticed.

Excel’s founder – ‘it’s good to double check’

PHE contacted Tim Harford, the English economist, broadcaster and journalist about it and he wrote it up in the Weekend FT of June 24. When he asked Bill Gates about the problem, in the course of an interview for BBC’s How to vaccinate the world, MicroSoft’s founder said: “I guess… they overran the 64,000 limit, which is not there in the new format, so… It’s good to have people double-check things, and I’m sorry that happened.”

We all have Excel on our computers—it’s part of MicroSoft’s Office suite and we’ve been using the spreadsheets for years even if they are a bit fiddly and not always accurate. In fact, Excelinexcel’s website lists How to Fix the eight most common errors and gives no less than 15 hot tips like “Names (i.e., Name Ranges) & Table Names shouldn’t conflict with cell references.” (Excelinexcel’s capitals and punctuation). Early laptop converts will remember you needed a whole new language to drive MicroSoft programs back then and Excel seems to be continuing this.

”It’s fine to celebrate success, but it is important to heed the lesson of failure.”“

Try Forecast 5 Today

Excel Dictates

Matt Parker, the author of Humble Pi, a book about mathematical mishaps and their consequences, notes that Excel’s own functionality combined with the mistaken assumptions of users will often introduce mistakes.

“As humans,” Parker notes, “we are not good at judging the size of large numbers.” A million seconds, he points out, is less than two weeks, but a billion seconds is 31 years. And even the mathematics of professionals can fail in critical situations, if our models of how things behave are incomplete.

“Type an international phone number into Excel, for example, and the program strips off the leading zeroes, which are redundant in a mathematical integer but not in a phone number. If instead you type in a 20 digit serial number, Excel will decide those 20 digits are a huge quantity and round them off, turning the last few digits into zeroes.”

NEW YORK – Protesters hold up an effigy of Enron’s Kenneth Lay during a march against the World Economic Forum (WEF) February 2, 2002 in New York City. By Anthony Correia.

Ho Hum

Harford reassures readers that trained accountants don’t make these sorts of mistakes. Felienne Hermans of Delft and Emerson Murphy-Hill of North Carolina State universities looked at over 15,000 spreadsheets and emails relating to Enron’s financial meltdown and found an astonishing number of spreadsheet errors. Their analysis shows:

24% of Enron spreadsheets had at least one formula with an Excel error
There was little diversity in the functions used in spreadsheets – 76% used the same15 functions
Spreadsheets were significantly poor at long calculation chains.

Moreover, 10% of Enron’s emails either refer or send spreadsheets and they frequently discuss errors and updates.

Excel is yesterdays budgeting solution. Today’s is Forecast 5.

Our software gives you complete confidence in the accuracy of your forecasts by using double-entry type control in its calculations.

Designed by accountants for accountants, it has all the features you need for accurately customising your forecasts. Detailed, comprehensive and fast, with this business planning software you can produce forecasts for profits and losses, funds flow, cash flow, and most importantly, your forecast balance sheet 10 times faster than with a spreadsheet.

Try all features and functionality of Forecast 5 today with our 21-day free trial.

Try Forecast 5 Today

Spreadsheets - accurate or archaic?

What to do when details of 16,000 Covid-positive cases disappear from Test and Trace spreadsheets?

Excel is yesterdays budgeting solution. Today’s is Forecast 5.

Try all features and functionality of Forecast 5 today with our 21-day free trial.

Contact Us