Excel and Big Decisions - 13 October 2023

Friday the Thirteenth is famous for its association with bad luck.  That makes today a fitting day to explore the use of Excel in corporations.

In a recent report, mistakes in Excel programming caused major, embarassing errors in hiring doctors in the UK (reference at the end).  The headline screams "Excel recruitment time bomb makes top trainee doctors 'unappointable'" and the sub-head explains that "Mangled mismatch of formats, macros, and VLOOKUP practice hits wannabe anesthetists".  Although this particular failure is significant to those affected, there is a larger problem that affects us all.  We start from two facts.

One.  Microsoft Excel spreadsheets are nearly impossible to debug, thus they contain numerous errors.

Two.  Microsoft Excel spreadsheets are used to make key decisions in nearly all businesses and institutions.

When we combine these, it is easy to see that key decisions in business and institutions are made based on bad data.  These include billion-dollar decisions as well as smaller decisions in hiring and promotion.  I spent years building budget and planning spreadsheets on behalf of my boss, often working with another, more skilled Excel practitioner and budgetmaster (hi, Nick!).  After many, many errors, we taught each other to put debug checks into our spreadsheets.  Not only did we sum across the matrix, but we summed down the matrix and compared the results.  As one example, we would build large matrices of spending or staffing numbers, and if the result of sum-across did not equal the result of sum-down, a cell in the sheet would turn red with a warning.  We were careful to cut-and-paste links rather then values, so that if the original values changed, the links would update.  (This cut-and-paste is a manual operation, so it was subject to errors, but we tried.)  These sheets would be used to create plans for hiring of staff and interns for the coming year, a critical decision that could cause us to fail to me business objectives if the numbers were wrong.  If our budget was too low, we might lack the staff necessary to do the work; if our budget were too high, we would overspend (and no one ever got Executive sympathy for overspending).  

In this particular report (from The Register), some bad hiring decisions were made for doctors in the UK, but we all know that mergers and acquisitions are decided based on Excel calculations.  M&A can be measured in billions of dollars.

To be fair, Excel, itself, is not the direct cause of the problem.  Excel is merely doing what the programmers are telling it to do.  Excel will happily sum 11 months of costs and report the sum to a reader expecting to see 12 months of costs.  And so on.  The fault is that Excel is not designed to allow programmers to detect and fix errors.  Further, most of the "programmers" are business people, not trained computer programmers, and so they lack many of the programming disciplines required to produce reliable, usable code (Excel macros, in this case).  

To fix this problem, Microsoft needs to add checking and debugging features to Excel and the Excel programming community needs to learn to use them.  I do not expect this to happen any time soon.

https://www.theregister.com/2023/10/12/excel_anesthetist_recruitment_blunder/?td=rt-3a