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

391–400 of 834 posts

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

#391

Post by TeMPOraL »

lordnacho wrote: post #385
TeMPOraL wrote: post #369

Earlier quoted context omitted.

My 4th order Runge-Kutta ODE solver I did in an afternoon in Excel the other day, because I didn't want to pirate Matlab to do a homework assignment, begs to differ :). Not to mention, the entire financial industry and many (most?) small businesses. Lots of people use Excel for various types of math, and it works fine, if you know what you're doing . Excel not being idiot-proof doesn't mean it's not usable. And that'…

> Not to mention, the entire financial industry That IS the dumpster fire. Trust me, the reason I know is that I used to be that guy who thought Excel was a great tool. I built derivatives spreadsheets, backoffice spreadsheets, trading systems with realtime data, all sorts of crap in Excel. Really, it's Stockholm Syndrome. People who previously had zero computing power at their disposal think they've found the hammer…

You bring up some very good points. But to those, I have a question: what is the alternative? Both now (for UK healthcare), and in general?

The "people who previously had zero computing power at their disposal" may be wrong in thinking "they've found the hammer that solves all problems" - but there's literally no other hammer available for them. They're not programmers, they won't write their own software (nor would they be allowed to). Any other option involves so much organizational overhead - both initial and ongoing - that it's a non-starter.

I agree that people routinely use Excel way beyond their own skills. But I haven't heard of any viable alternative.

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

#392

Post by sudosysgen »

TeMPOraL wrote: post #369
srtjstjsj wrote: post #345

Earlier quoted context omitted.

Excel never works for math, at any scale. It's a constant trashfire of "looks fine" until it shows a result someone doesn't want, and then an error is found that changes results and the cycle repeats. Excel is nice for visualizing and browsing data you already have, and informally searching and sorting for hypothesis generation.

My 4th order Runge-Kutta ODE solver I did in an afternoon in Excel the other day, because I didn't want to pirate Matlab to do a homework assignment, begs to differ :). Not to mention, the entire financial industry and many (most?) small businesses. Lots of people use Excel for various types of math, and it works fine, if you know what you're doing . Excel not being idiot-proof doesn't mean it's not usable. And that'…

If you don't already know, Octave is a very good free and open source Matlab compatible interpreter and solution. Was of great use in my time in college.

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

#393

Post by commandlinefan »

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…

> another lunatic who says something called "pigsqueal"

And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".

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

#394

Post by dkarp »

jen729w wrote: post #285
jononor wrote: post #199

Always use checked pre- and post-conditions in a data pipeline. This simple post-condition would have caught this issue: The sheet after merge operation must have a number of rows equal to the sum of number of rows for all merged sheets. Assuming this is a merge of a standardized input, then another post-condition might be: The number of columns in output shall equal the number of columns in the input. Might want to…

Even just within my own sheet I always build in some sort of idiot-check, the assumption being that I’m the idiot. Do a SUM and match it against a COUNTIF on another column, or something. It doesn’t really matter what it is but if the data is important at all , I always scatter little checks throughout. The case described in this article sounds like someone who just doesn’t know how to use Excel. I mean why in the na…

I’d guess they wrote a script to merge the csv file and export in xls. Xls might have been the default of the library they used in a rush. I think this may be the case with some python libraries that generate excel files as xlsx is a more complicated format.

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

#395

Post by GuB-42 »

nickff wrote: post #333
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.

Here is an excellent video about this subject (in French, but the auto-translate subtitles are understandable) https://www.youtube.com/watch?v=yeX_Zs7zztY

To summarize:

- The first two thirds of the video is a retelling of the story, the last third is an analysis of the data

- There are two problems in the original paper: a weird way of computing averages, and a mistake in their Excel file.

- There is a correlation, but it is weak (R2=0.04), and if there is an inflection point, it is around 30-40%, not 90%.

- That paper is likely to have been selected by politicians to support their policies instead of influencing them (confirmation bias).

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

#396

Post by JoeNr76 »

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…

Well, I don't see why developing an application for transforming csv files to xls files would go faster than developing an application that uploaded csv files to a database.

I could see that i could be a rush job problem, but in this case they're not gaining any time.

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

#397

Post by iagovar »

morsch wrote: post #29
daanlo wrote: post #16

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

CSV seems like a good choice for this kind of tabular, linear data.

CSV is terrible for anything containing human text though. It's a nightmare.

I use SQLite as files a lot for this reason.

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

#398

Post by mordae »

ksenzee wrote: post #363
srtjstjsj wrote: post #345

Earlier quoted context omitted.

Excel never works for math, at any scale. It's a constant trashfire of "looks fine" until it shows a result someone doesn't want, and then an error is found that changes results and the cycle repeats. Excel is nice for visualizing and browsing data you already have, and informally searching and sorting for hypothesis generation.

> Excel never works for math, at any scale. That is patently untrue. I've borrowed and lent money based on Excel spreadsheets, more than once. That's small-scale math with real-life consequences, and Excel is an excellent tool for the job.

Couch GnuCash Cough

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

#399

Post by iagovar »

pbhjpbhj wrote: post #291
langitbiru wrote: post #150

So the limit for Excell is 65,000 rows for the old format, and one million-plus rows for the new format (from the article). Then I wonder, is there any tool that mimic Excell but with Sqlite as the backend? The limit of rows in Sqlite is 2 raised to the power of 64 (18446744073709551616 or about 1.8e+19). https://sqlite.org/limits.html

You could open a DB as a table view in OO.org, so presumably you can in LibreOffice. I'm surprised Excel doesn't integrate with Sqlite DBs. My naive view would expect tables sheets; rows tuples to be easy to do (for MS) and just don't touch the relational aspects??

Does it work with SQLite? Is it practical?

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

#400

Post by javagram »

rbanffy wrote: post #361
gxs wrote: post #334

Earlier quoted context omitted.

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

> 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. Doing code reviews in Excel is hard if the developers are pathologically disciplined. It's impossible most of the time. And so is debugging. It's very unlikely any such system would face these limitations and would silently ignore data the same way Excel did.

> It's very unlikely any such system would face these limitations and would silently ignore data the same way Excel did.

I’ve seen plenty of production systems that ignore or hide errors. Sometimes they’re still logging them, but it just goes to some log store or file that the team doesn’t check until their customers or support team inform them that it’s broken.

Good practice? No. But there are plenty of ways to mess up a non-Excel system and get something that works worse.

Post reply on HN