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

501–510 of 834 posts

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

#501

Post by nabilhat »

rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

Over-13-years old Excel already opened delimited flat files natively, and it threw warnings when you opened a .csv file that exceeded the worksheet row limit. Someone in the distant past was just clever enough to hack together the script (with no error handling) without being curious enough to know it wasn't necessary in the first place.

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

#502

Post by croes »

TeMPOraL wrote: post #316
croes wrote: post #280

Earlier quoted context omitted.

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 JavaScript autoconverts all your numbers to floats, introducing errors every time you touch them. Meanwhile most other popular languages will happily wrap your fixed-size integers around on overflow, or silently perform integer division with truncation when you don't append a decimal fraction part manually to any of the numbers. Point being, all tools come with strange caveats that one needs to be familiar with.…

The typical users of javascript ate programmers, they know that errors. The typical users of Excel most of the time aren't aware of Excels weaknesses. And good look finding these errors in large data sets. There is a difference between visible and lucid, especially if you do not expect errors.

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

#503

Post by tomc1985 »

bobbylarrybobby wrote: post #494
rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

The issue with is excel is that its failure mode is suboptimal. Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc). But excel just throws away your data?

Indeed, you have to manually inspect results to verify correctness. If you're lucky maybe your error will result in one of those weird error values being shown instead, but way too often that is not the case

Not to mention that complex formulae are still usually expressed as a bunch of gobbledygook in the cell value textbox, which is about as easy to parse as minified Javascript. And that's to technical users like ourselves.

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

#504

Post by intended »

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…

This is what it most likely is. Analogous to starting out on an easy to hack together tech stack, succeeding, and dealing with tech debt when something goes wrong.

I guess its easier to understand since everyone uses excel, however it does end up giving a halo of blame to excel, as opposed to human processes.

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

#505

Post by [deleted] »

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…

[deleted]

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

#506

Post by croes »

vkou wrote: post #331
croes wrote: post #280

Earlier quoted context omitted.

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

Band saws, C++, Javascript have a different user group than Excel. Most of them have been trained on the tool they use. If Excel were a band saw, there would be a lot more people missing fingers.

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

#507

Post by ByteJockey »

Nightshaxx wrote: post #456
cabalamat wrote: post #450

Earlier quoted context omitted.

> It's just that you get a lot of "coding is a thing I have to do in order to get to some target", and so people think that once they've finally bashed out their spreadsheet, they've figured it all out. I have a friend who wrote a 641-line long bash script to automate a web site. It doesn't use subroutines anywhere, the body for the program is a 550 line long loop with multiple if statements and loops inside it. He t…

Wha...... What?? What school or book did he read that made him think that was a good idea?

Could be worse. Could be a 641 line perl script.

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

#508

Post by croes »

airstrike wrote: post #314
croes wrote: post #280

Earlier quoted context omitted.

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.

You can find idiosyncrasies about any tool. Excel is a fine tool for 99% of use cases. Sure, it's quirky here and there, but the sheer amount of work that gets done globally in Excel should be testament enough to its value. Highlighting edge cases to bash on the tool is either myopic or intellectually dishonest

Excel is good at what it was developed for. Unfortunately, it is often used for other things. For example, in this case as a database. This leads to the fact that departments build solutions that work at first sight but have errors in special cases that are difficult to detect and debug.

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

#509

Post by croes »

dtech wrote: post #337
croes wrote: post #280

Earlier quoted context omitted.

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

They renamed 27 genes in the past years to avoid Excel errors https://www.engadget.com/scientists-rename-genes-due-to-exce...

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

#510

Post by curiousllama »

bobbylarrybobby wrote: post #494
rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

The issue with is excel is that its failure mode is suboptimal. Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc). But excel just throws away your data?

If it fails, it’s often silent. If not silent, hidden. If not hidden, misleading, if not misleading, opaque. If not opaque, trivial.

Excel is a wonderfully powerful tool that’s very bad at handling errors clearly.

Post reply on HN