A British medical office has made a right mess of selecting trainee anesthetists by the misuse of Excel. Let’s look at what happened and some ways you can avoid similar mistakes.
As told in The Register, the UK Anesthetic National Recruitment Office (ANRO) had the job of selecting candidates from across seven regions to get coveted training positions.
That meant getting lists from each region with candidate details including their score on an interview. Unfortunately there was no consistency in the seven Excel lists that arrived for merging into a single candidate list.
The merging process wasn’t consistent with some staffers using manual pasting of rows while others used (misused) VLOOKUP().
The Welsh list didn’t have interview scores for each of the 24 candidates, instead they were ranked from 1 to 24. Those rankings were wrongly used as scores in the master sheet, meaning all the Welsh applicants appeared to be ‘unappointable’ (scoring 24 or less) compared to other regions. Worse, the best Welsh candidates (ranked 1, 2 etc.) showed up very low on the master list.
This wasn’t a small problem. Overall, 35 of the 400 candidates where affected and the Welsh applicants weren’t offered places near them in Wales.
Some modest suggestions
It’s a cautionary tale of the traps in Excel, procedures and collaboration.
If you’re collecting data from different groups, make sure the information is consistent. Ideally distribute a blank templates Excel sheet for each contributor to fill in and return. That ensures normalized data coming in and, if there’s any mistakes, they are the fault of the sender not the merging.
With consistent data files, you can use PowerQuery to merge the data files from a folder which automatically update the master list when new or revised data arrives. We explain this trick in our book Real-Time Excel – search for ‘Data from a Folder’.
When collaborating on a sheet or document, make sure everyone involved is ‘on the same page’. In other words, doing the same thing. Part of the anesthetist problem was that staffers were adding data rows in different ways.
Still using Vlookup()? In Excel 365 or 2021, it’s far easier and safer to use Xlookup().
Check the results. Have a look at a merged or imported list in different ways to see if there’s any anomalies or unusual extremes. Modern Excel has an Analyze Data option which automatically shows some extremes in the list. Even some simple charts or sorting/filtering the list might have shown that all the Welsh candidates had unusually low scores.
And it got worse …
The problem got worse even after the Excel list troubles were sorted out and ANRO decided to offer more positions.
The recruitment system ‘Oriel’ had a bug meaning that instead of 10 more job offers being sent out they sent out 26 offer messages.
ANRO did the honorable thing and offered places to the additional 16 accidental candidates.
The only upside of this mess is that the NHS has a severe shortage of anesthetists so the accidental extra training positions might eventually help improve patient care.
It’s not the mistake, it’s how you deal with it
As often happens, a lot of the complaints aren’t about the mistake is handled. Company management and politicians seem to often to try hiding a problem, which only makes it worse when the news finally comes out.
The Excel mistakes happened in 2021 but only after a Freedom of Information request did the whole mess become public in 2023.
