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

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

#331

Post by vkou » Mon, Oct 05, 2020, 8:54 PM UTC

croes wrote: post #280

airstrike wrote: post #268

Earlier quoted context omitted.

You could say the same thing about writing tests. There's nothing wrong with Excel. Build an organized spreadsheet with clear separation of data and presentation, and write checks throughout and you will end up with a perfectly error-free workbook.

Nothing wrong? Excel autoconverts values if it thinks it knows the data type of a column and Excel treats 1900 as a leap year. Genetic researchers changed the names of genes to prevent auzoconveesion errors.

And a band saw will happily cut off your hand if you push it into the moving blade. That's why the best practices of operating one suggest that you never keep your hands anywhere near the cutting edge.

Excel's idiosyncrasies are very much on the level of the typical productive computing tool. They are less maddening than half the featureset of C++, three quarters of the featureset of Javascript, and 110% of the featureset of bash.

Despite that, people manage to get work done using bandsaws, C++, Javascript, and the occasional shell script.

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

#332

Post by aphrax » Mon, Oct 05, 2020, 8:56 PM UTC

rvba wrote: post #292

NelsonMinar wrote: post #263

Earlier quoted context omitted.

Silent? Are you positive? Not doubting you but if so; WTF?!

I just tried to reproduce it and got a big warning message that says that data will be lost. You can disable this message (per file basis), if for some reason you dont want to see it. As far as I remember this message existed since Excel 2007 (which introduced .xlsx format), so the guy is simply lying.

Certainly not lying but it was a few years ago. Not sure which version but not 365

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

#333

Post by nickff » Mon, Oct 05, 2020, 8:57 PM UTC

nabla9 wrote: post #322

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 proposa…

I think you're overstating both the paper's impact, and the significance of the error.

Most such papers are used to support existing policy preferences, not drive them. In addition, the error didn't reverse or erase the correlation, it diminished it, and removed the inflection point from the curve.

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

#334

Post by gxs » Mon, Oct 05, 2020, 9:00 PM UTC

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…

Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release.

On top of that, there is STILL room for human error as a user is still the one actually inputting data and designing the experiment/data flow.

I'm going to get mauled on this forum given the audience, but parent comment reeks of the technical elitism on this forum and the tendency to immediately condemn anyone who is using excel.

Excel works great - I've personally built some very complicated models that work just fine. Goes through the same process of QA and line-by-line checking of requirements.

Not sure what the big deal is - mistakes happen in excel or otherwise.

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

#335

Post by codeulike » Mon, Oct 05, 2020, 9:00 PM UTC

jl6 wrote: post #315

It’s fun to point and laugh, but is this really just the consequence of an unprecedented health emergency demanding a rush job? As far as bugs go, it doesn’t sound that bad. They didn’t lose data - they just processed it late? And they spotted it within days/weeks, and have a workaround/correction already? And it’s only the reporting that was wrong, not the more important part where they inform people of results? I’d…

Good points

Partly I think the Excel bit is news because Excel (and mistakes made with Excel) is easily relatable to people. But bugs always come up and most other bugs are just as stupid. If it had been an off-by-one loop error in some C code somewhere it would be just as dumb but you'd get none of the facepalm memes all over Twitter.

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

#336

Post by retbull » Mon, Oct 05, 2020, 9:00 PM UTC

sjansen wrote: post #307

strbean wrote: post #293

Earlier quoted context omitted.

Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?

Probably not while complying with privacy laws. I would be much more upset to find out a governemnt employ on a deadline used Google Sheets or Airtable to share my medical data instead of an Excel doc on a secure government file server.

I don't really agree with much of google any more but I was at one point fairly involved in a project there. They do have some more secure systems for government use that isn't the crazy leaky ad cesspool gmail and docs is.

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

#337

Post by dtech » Mon, Oct 05, 2020, 9:01 PM UTC

croes wrote: post #280

airstrike wrote: post #268

Earlier quoted context omitted.

You could say the same thing about writing tests. There's nothing wrong with Excel. Build an organized spreadsheet with clear separation of data and presentation, and write checks throughout and you will end up with a perfectly error-free workbook.

Nothing wrong? Excel autoconverts values if it thinks it knows the data type of a column and Excel treats 1900 as a leap year. Genetic researchers changed the names of genes to prevent auzoconveesion errors.

The gene in question was "Membrane Associated Ring-CH-Type Finger 1", or MARCH1. Excel converted this to March 1st. They renamed it to MARCHF1

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

#338

Post by bradford » Mon, Oct 05, 2020, 9:01 PM UTC

ACow_Adonis wrote: post #287

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…

the secret to using Excel well in large complex tables/spreadsheets is the same secret as coding with large complex programs. You use structure, design away the complexity, implement constraints, error checking and tests. Note it's true that an inexperienced person is liable to make spaghetti code either way, but there's nothing fundamental about a spreadsheet format that makes it inherently unusable for a lot of sma…

> You use structure, design away the complexity, implement constraints, error checking and tests.

I've never thought of Excel as an ideal tool for any of these things. I'm struggling to think of how it would have change-verifications/tests in the same way that software projects do.

(I'm certainly open to the possibility that I'm ignorant/unaware on this subject)

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

#339

Post by TeMPOraL » Mon, Oct 05, 2020, 9:01 PM UTC

fivre wrote: post #302

TeMPOraL wrote: post #160

Earlier quoted context omitted.

That's why Excel wins. Because it's right there, infinitely adaptable to any change in workflow you may need to make. No need to send official requests through half a dozen layers of management to get the IT people to maybe, in half a year, add a column to the database. No need to involve 20 people for a year to create new software for a new workflow. You just agree with your co-workers on a change, swap a column, ad…

I mean, yeah, but pretend Excel is, idk, sword arms. You get a lot of work done by whirling around the office, spinning your swords, cutting off a variety of limbs and heads in the process. You get to the end, meet the few survivors, staring dismayed at the trail of blood and dismemberment behind you, and you yell, "BUT I GOT THE JOB DONE!" You're not wrong, but... I wish there were some alternative where we instead…

But the negative externalities don't seem all that big, considering. For every problem like this, you get countless millions of person-hours and dollars saved in work that wouldn't have happened at all, or would have been delayed, because of the necessity and cost of creating complex IT systems to support it.

If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant about sharp objects. The alternative then would be a central processing factory that takes several years and millions of dollars to built, and which has to be turned off for a month several times a year, to change the shape of the blades used by the automated cutters.

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

#340

Post by aphrax » Mon, Oct 05, 2020, 9:02 PM UTC

NelsonMinar wrote: post #263

aphrax wrote: post #134

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good..

Silent? Are you positive? Not doubting you but if so; WTF?!

Yep, I'm positive but as said elsewhere this is a few years back. I recall sending the data out and getting some pretty stern feedback (this is why I recall it) Didn't make the mistake after that...