Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Missing Covid-19 test data was caused by the ill-thought-out use of Excel

bbc.com

Post reply on HN
Print view View original

321–330 of 834 posts

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#321

Post by Ma8ee »

mjw1007 wrote: post #181

So, what we know is: « PHE had set up an automatic process to pull this data together into Excel templates [...] When [the row limit] was reached, further cases were simply left off. » The terrible thing here is dropping data rather than reporting an error and refusing to run. It isn't clear what piece of software was behind this "automatic process". Clearly the responsible humans are to blame. If the software that d…

I had to import data from Excel documents into MS SQL Server. Since this was supposed to be a one time thing I used the import wizard. Some values where quietly ignored because the cells happened to have different formats than the surrounding cells. They looked exactly the same, so it took me a little while to figure it out. And if I wouldn't have done some simple sanity checks those errors would definitely not have been discovered before it was too late.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#322

Post by nabla9 »

The most consequential Excel error was The 2010 Reinhart-Rogoff error in the paper "Growth in a Time of Debt" https://www.nber.org/papers/w15639

The paper claimed that average real economic growth declines 0.1% when natonal debt rises to more than 90% of gross domestic product (GDP). When you correct the error it shows 2.2% average increase in economic growth.

Paul Ryan used it in the US for Republican budget proposal to cut spending and it was also used in EU to implement Austerity policy that hurt people.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#323

Post by hinkley »

beervirus wrote: post #294
hinkley wrote: post #207

I hate solving problems and not knowing how I did it, so this particular experience has stuck in my craw. Trying to develop a budget to pay off debts, my partner made this elaborate Excel spreadsheet and the output was that basically she had no spending money and I had very little, until one or both of us got a raise. It was far more austere than either of us were willing to go. So I started over using a different eq…

If you're trying to figure out why the spreadsheets had different results, I'd start here: > I started over using a different equation

Try as I might, I've never found a way, before or since, to push piles of stuff around and end up with a larger sum total.

I suppose it's possible I've discovered Dark Money but I doubt it.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#324

Post by dredmorbius »

There's a long history of spreadsheet errors and literature on same going back a few decades now.

In a business sim class in college a couple of decades back, I discovered that the Lotus spreadsheets (as I said: a couple of decades back) had a totalling error which double-counted individual row totals in the bottom line (everything was twice as profitable as the spreadsheet indicated).

At an early gig, one of the senior developers instituted a practice of code walkthroughs on projects (only a subset of them). One of these involved, you guessed it, a spreadsheet (we used a number of other development tools for much of our work), in this case Excel. Again, numerous errors which substantively changed the outcome of the analysis. One of the walkthrough leader's observations was that you could replace all of the in-cell coding with a VBA macro making debugging far easier (all the code and data are separated and in one place each).

The particular analyst whose project this was: he insisted to the very end that this "wasn't a program" and he "wasn't a programmer" and that the walkthrough didn't apply to his situation. Despite the errors found and corrections made.

At the time (mid 1990s) the walkthrough lead turned up a paper from a researcher in Hawaii on the topic. I'm not certain it was Raymond Panko, but his 2008 paper (a revise of a 1998 work) discusses the matter in depth:

https://web.archive.org/web/20070617041554/panko.shidler.haw...

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#325

Post by BSG-Actual »

jjuhl wrote: post #312

Excel? Really ? They are using that crap for nation wide reporting? Wow, mind blown. Epic fail.

A non-technical senior manager who was in charge of a project I worked on went on to manage England's contact tracing. Not sure if he was directly responsible for picking Excel, but everyone who worked under him was not the least bit surprised when this news came out.

The project failed spectacularly if you were wondering.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#326

Post by [deleted] »

TeMPOraL wrote: post #286
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system". The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentica…

[deleted]

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#327

Post by hanoz »

lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

The dubious wisdom of choosing Excel for this aside, how many people working in (1.) computing and (2.) statistics saw a file with exactly 65536 rows, with nary an eyebrow raised? And I mean the first time they saw it, not the second, third or fourth day in a row.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#328

Post by chasd00 »

interesting, i was on a conf call 3 hours ago that was an almost identical use case. Excel gets used because word comes to a director at a health department that says "we need to collect testing results from 500 different facilities in a way their, and our, existing systems do not support" and they're given maybe 3 days to get it done along with the rest of their job. Putting together a technical team would take a week minimum let alone requirements gathering and analysis. So what do they do? They fire up Excel, add column headers that represent the data they need, and then send it to a Sr. Manager and tell them to make it happen.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#329

Post by Reason077 »

happytoexplain wrote: post #296
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.

Excel can handle the volume of data just fine. The (very outdated) .xls file format with its 65,536 row limit was the problem here.

Re: Missing Covid-19 test data was caused by the ill-thought-out use of Excel

#330

Post by ksk »

beagle3 wrote: post #85

This is indeed user incompetence, but so far every news source quoted the official PR and said it was an "Excel Problem". Microsoft PR was caught unprepared - I wonder how they'll re-spin it in the next few days (and for the first time that I can recall, a Microsoft product was wrongly blamed...) Pay attention, how every time there's a Windows virus or worm, it's a "Computer Virus", but in the (extremely rare) occasi…

Is your view based on data available to everyone else? If so, that would be nice to see. Its far easier for you to convince others with data. Why would anyone want to change your view?
Post reply on HN