1. If a large share of hospitalised people are vaccinated, that’s a sign of success. It has been common to see headlines noting that a substantial minority of people who have been hospitalised or even killed by Covid have been fully vaccinated. These numbers suggest vaccine failure is alarmingly common. The fallacy only becomes clear at the logical extremes: before vaccines existed, everyone in hospital was unvaccinated; if vaccines were universal, then everybody in hospital would be vaccinated. Neither scenario tells us whether the vaccines work.
So try this. Imagine that 1 per cent of the unvaccinated population will end up in hospital with Covid over a given time period. In a city of a million people, that would be 10,000 hospital stays. Now let’s say that 950,000 people get fully vaccinated, that the vaccine is 95 per cent effective against hospitalisation, and that the vaccine doesn’t reduce transmission (although it does). Here’s the arithmetic: 500 of the 50,000 unvaccinated people end up in hospital. A total of 9,500 of the vaccinated people would be at risk of a hospital visit, but the vaccine saves all but 5 per cent of them. These unlucky 475 still go to hospital. The hospital contains 500 unvaccinated and 475 vaccinated people — almost half and half — which makes it seem as though the vaccine barely works. Yet when 95 per cent of people take a 95 per cent effective vaccine, hospital visits fall from 10,000 to fewer than 1,000.
Somewhere in PHE’s data pipeline, someone had used the wrong Excel file format, XLS rather than the more recent XLSX. And XLS spreadsheets simply don’t have that many rows: 2 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.
The idea of simply running out of space to put the numbers was darkly amusing. A few weeks after the data-loss scandal, I found myself able to ask Bill Gates himself about what had happened. Gates no longer runs Microsoft, and I was interviewing him about vaccines for a BBC program called How to Vaccinate The World. But the opportunity to have a bit of fun quizzing him about XLS and XLSX was too good to pass up.
I expressed the question in the nerdiest way possible, and Gates’s response was so strait-laced I had to smile: “I guess… they overran the 64,000 limit, which is not there in the new format, so…” Well, indeed. Gates then added, “It’s good to have people double-check things, and I’m sorry that happened.”
Exactly how the outdated XLS format came to be used is unclear. PHE sent me an explanation, but it was rather vague. I didn’t understand it, so I showed it to some members of Eusprig, the European Spreadsheet Risks Group. They spend their lives analyzing what happens when spreadsheets go rogue. They’re my kind of people. But they didn’t understand what PHE had told me, either. It was all a little light on detail.