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

Scientists rename human genes to stop MS Excel from misreading them as dates

theverge.com

Post reply on HN
Print view View original

181–190 of 518 posts

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#181

Post by FabHK »

mnw21cam wrote: post #71
ChrisLomont wrote: post #43

Earlier quoted context omitted.

>based on its glorified bugs If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? Probably 99.999% of users would prefer this auto-understanding. To make users have to then select cell formatting and select date is a terrible UI decision. It's odd to call such a useful feature a bug. >Excel is fundamentally not suited to analysing *omics data But it's excellent for a ma…

> If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? It should treat these as dates, unless told otherwise. But Excel goes one step further, and throws away the text you originally typed in. That means, if you type "DEC1" into a spreadsheet, see it looks stupid, and change the data type of that cell to "Text", then it's too late, and Excel will show "37226". It has de…

Excel does that, largely: cells have a datatype, a value, and a format (among other properties). Changing the format leaves the value unchanged.

However, when you input something into a cell, Excel has to parse it and decide what value and datatype to assign to it. That is just inevitable, and the same everywhere: in Python, `x = 3` assigns an integer, `x = 3.` a floating point, and `x='3'` a string. Similarly hints (or explicit specifications of type) are possible in Excel.

However, given that, what you typed originally is not recoverable anywhere (Python doesn't record whether you wrote `x=3.` or `x = 3.00`, why should it?).

So, to the extent that it is reasonable, Excel does what you request, and to the extent it doesn't do it, it's not reasonable, I'd say: A cell need not maintain the original pre-parsing input.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#182

Post by jrott »

lordnacho wrote: post #154
jrott wrote: post #88

Earlier quoted context omitted.

Yeah excel produces hairballs so easily. From a coder perspective I've only seen excel workbooks that are bad or terrifying. On the other hand a ton of people that don't write software for a living and have no interest in code manage to produce things that help them do their job and automate a ton of tedious stuff.

Most people don't write novels or poetry for a living either, but we still need some grasp of basic writing skills like grammar and structuring. We're already moving towards a future where a lot of people have coding as part of their job, and we should expect them to know a few things.

Oh I totally agree. It’s just worth acknowledging where most bad excel spreadsheets come from or at least it gives me more patience with them.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#183

Post by danso »

The article links to a paywalled Nature article [0], titled "Guidelines for human gene nomenclature". I googled that title to find a free and open version, and came across what seems to be the official page for HGNC's (extensive) naming guidelines [1], though what's currently published seems to be an older standard, originally published in 2002 [2]

[0] https://www.nature.com/articles/s41588-020-0669-3

[1] https://www.genenames.org/about/guidelines/

[2] https://pubmed.ncbi.nlm.nih.gov/11944974/

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#184

Post by FabHK »

glofish wrote: post #94
ChrisLomont wrote: post #43

Earlier quoted context omitted.

>based on its glorified bugs If someone types MARCH1, DEC1, OCT4, SEPT1 into a column, why should Excel not treat these as dates? Probably 99.999% of users would prefer this auto-understanding. To make users have to then select cell formatting and select date is a terrible UI decision. It's odd to call such a useful feature a bug. >Excel is fundamentally not suited to analysing *omics data But it's excellent for a ma…

It is shortsighted to call this behavior a "useful feature". It was and is a grave and serious mistake. Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience. The damage that this "feature" caused and will keep causing goes far beyond the domain of bioinformatics. Even in bioinformatics things have not been "solved", for the next decade the same gene will have tw…

> Automatically converting SEPT1 to a date caused far more damage than the benefit of the fleeting convenience.

Who are you to judge? For the novice, even for most users it'll be what they want. And for the expert (or even intermediate user) it is trivial to override.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#185

Post by bronzeage »

Honestly, Microsoft should fix Excel to stop corrupting data by default. This is 100% Microsoft's fault that an international organisation resorts to workaround renaming things because they needlessly parse and modify input in a default configuration.

You can also say pretty much many of Microsoft security issues over the years boil down to their programs needlessly overthinking and parsing perfectly valid input, for obscure reasons.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#186

Post by gundmc »

shpongled wrote: post #159
kaesar14 wrote: post #151

Earlier quoted context omitted.

What's your source on it not being difficult to find biologists who are adept at using python, R, or C? Most biologists operating in private industry or academia have many years of training in their fields and many have learned their computational tools as they've gone on, meaning they've never received proper training. It seems dubious to claim that there's this neverending source of well trained biologists who are…

I would say the number of biologists who actually understand programming is extremely small. I've been programming for fun for ~15 years, and I'm about to finish a PhD in chemical biology (e.g. I started programming in C far before I started learning biology). You might occasionally run into someone who is passable - at best - with R or Python. But most of the code they might write is going to be extremely linear, an…

To be fair, linear code is often totally sufficient for most types of data analysis. Biologists don't really need to understand design patterns or polymorphism, they just need to not make computational mistakes when transforming the data.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#187

Post by throwaway936482 »

rkachowski wrote: post #80

My mind is blown that Excel's usability is so bad that the representation of the human genome itself has to adapt around it's undesired behaviour. As in, the history of genetics research is now irreversibly linked with the shortcomings of this one software product, which just happens to be incapable of describing the genetics of the organisms that created it.

Excels usability isn't bad though. Which is why everyone uses it. It falls down in this (and lots of other cases) but if you want to see what good usability looks like, look at what people use.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#189

Post by acid__ »

mcv wrote: post #162

> "Why, exactly, in a fight between Microsoft and the entire genetics community, was it the scientists who had to back down?" Back down? Or pick a better tool. If Excel proves to be an unreliable tool for your job, use a better one. Alternatives exist, ranging from Google Docs, and LibreOffice, to simpler light-weight spreadsheets. Or possibly more specialist tools. Why does everything always have to be put in Excel…

Excel may have failed in this specific task, but let’s not pretend like its functionality doesn’t run circles around Google Docs and LibreOffice.

Excel is a “pretty darn good” tool for 95% of tasks. If your work has highly varied workflows, then that flexibility more than makes up for its failures on the last 5%.

If you have very specific workflows on the other hand, you may find value in replacing Excel with a specialist tool. But let’s not pretend that specialist tools don’t also have their own shortcomings; at best they’ll achieve 99.9% coverage of tasks.

Re: Scientists rename human genes to stop MS Excel from misreading them as dates

#190

Post by bronzeage »

curiousllama wrote: post #51

Excel datetime functions are garbage. you have to explicitly, manually tell it not to format things as dates, the most destructive data type, but it doesn't act that way for other formats (e.g., $ doesn't turn things into accounting format). That said: every datetime function I've ever written is also garbage so... glass houses, I guess?

Which is exactly why feeding all your input into a trash input parsing function by default is a horrible idea. If dates handling was just 1 extra button you click, the problem wouldn't exist. Overzealous default behavior, the #1 sin of Microsoft.
Post reply on HN