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

41–50 of 518 posts

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

#41

Post by hairofadog »

Legogris wrote: post #27
hairofadog wrote: post #14

Earlier quoted context omitted.

I’ve been wishing for something like this lately in the apple ecosystem: sort of a “plain text” tabular editor that can handle large data sets. Numbers feels like it’s designed for presentations more than data crunching, and Excel (or at least the price of excel) would be overkill for my needs (which largely consists of data cleanup, spot-checking, and quick calculations). I’d happily pay for an IA-Writerish spreadsh…

Not exactly what you're asking for, but what about libreoffice?

It’s been a while since I fooled around with that. I’ll give it another look.

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

#42

Post by proactivesvcs »

DethNinja wrote: post #11
lunchladydoris wrote: post #2

Or perhaps stop using Excel?

What’s the alternative?

And what are the alternative's silent pitfalls, areas of common misunderstanding et al? Replacing a tool because it is not used properly is just asking to receive another tool to be misused.

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

#43

Post by ChrisLomont »

azalemeth wrote: post #23
jordigh wrote: post #7

To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?" The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally ag…

> " To people calling, "why do they use Excel?" that's like asking "why must we be subjected to gravity?"" I respectfully disagree with this. Excel is fundamentally not suited to analysing *omics data. It's often the default program affiliated with a .csv filetype on people's computers, but trying to get an entire field of scientific research to rewrite itself based on its glorified bugs is...wrong, in my opinion. If…

>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 massive range of basic analysis and especially for a unified platform to share results, which is why everyone uses it. It's good for publication or proposal steps of creating graphs from data output from other more specialized systems, that usually lack the flexibility and widespread use that Excel has.

If my group uses some specialized, often in house, analysis software, my results cannot be spread to others unless I put them into a more common and accessible format. And nothing in the genomics and wider scientific community has anywhere near the widespread availability as Excel.

You might as well complain they also use word docs instead of Latex or troff. Not everyone wants to spend days fighting software instead of doing work.

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

#44

Post by lordnacho »

The problem is that your average business user of Excel thinks type safety is a bug, not a feature. If Excel enforced types instead of guessing on your behalf, a lot of people would complain.

And it would be really hard to explain why it is sometimes useful to have constraints to someone who normally sees Excel's flexibility as its main strength.

I don't think I've ever seen a non coder use Excel in a sensible way: maintainable, easy to change, consistent meanings of entities, simple to understand. It's always a ball of spaghetti, even for pretty small projects. Loads of VLOOKUPs, external DLLs, buttons everywhere, vba files galore. Plus they lay out the cells haphazardly.

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

#45

Post by curiousllama »

yummypaint wrote: post #4

Why in the world is excel the application of choice? Last time I tried to use it (for much less complicated things than genetics), it choked and became unusable when the filesize exceeded about 6MB. I have yet to encounter a spreadsheet oriented task that isn't better implemented in gnumeric. Maybe we should also shorten all publications so adobe reader can display them without crashing?

I work in excel every day and, if this is true, you needed to spend like 30 seconds on google to find a half dozen solutions to this. Turn off auto-calculations, delete pivots, write fewer vlookups, etc...

It's roughly the equivalent of saying "whenever my C program gets too big the 'core dumps,' whatever that means - a totally useless language"

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

#46

Post by brisance »

hairofadog wrote: post #14
lvturner wrote: post #3

I think another question is that why isn't there a viable alternative to excel for this particular use case? Is there, perhaps, a market for niche spreadsheet applications that serve one particular market?

I’ve been wishing for something like this lately in the apple ecosystem: sort of a “plain text” tabular editor that can handle large data sets. Numbers feels like it’s designed for presentations more than data crunching, and Excel (or at least the price of excel) would be overkill for my needs (which largely consists of data cleanup, spot-checking, and quick calculations). I’d happily pay for an IA-Writerish spreadsh…

Have you taken a look at Wizard?

https://www.wizardmac.com/support.html

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

#47

Post by meow1032 »

jordigh wrote: post #7

To people asking, "why do they use Excel?" that's like asking "why must we be subjected to gravity?" The whole world's data ultimately comes from or ends up in an Excel spreadsheet. Sure, we might use other intermediate data storage methods, but in the end it's going to go into some scientist's or some politician's computer, and by golly it's gonna be in Excel. Trying to rally against Excel is like trying to rally ag…

I don't disagree completely with this, but just want to point out that it's kind of a bad smell to have computational biologists who are - as someone in the article puts it - computationally illiterate. I have met lots of these types over the years, and usually their methods are kind of a gong show. If you can't properly sanitize your data inputs on your column headers, why should I trust that you've treated the rest of your data properly?

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

#48

Post by LatteLazy »

qayxc wrote: post #30
LatteLazy wrote: post #26

It's honestly amazing that Excel hasn't fixed this issue. It's pisses off an enormous number of users especially in basically any non-US country (even if 01/02 is a date, it isn't the second of January in most of the world...)

That's because it's not an issue at all. It's people using a tool without knowing said tool. You can disable auto-formatting (or even better yet - set the column data type) with a simple click.

Even if you correctly format a column, excel will ignore that if it sees something that looks like a date. That's part of the problem: this isn't just automatic formatting, it's very aggressive and hard to turn off. Plus you cannot revert the changes excel has made: turning 01/02 is converted to an ibt in 46000 range. So reformatting the cell doesn't get the original input back, it just lands you with a bunch of ints.

Back in the day I actually wrote a function that would undo this for some sheets that people kept breaking...

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

#50

Post by xkgt »

Duller-Finite wrote: post #8

Excel isn't the program of choice for most scientists and computational biologists, who typically use R, python, or command line tools. However, we often get data from other scientists or reanalyze data from other groups that can have these errors. It's so frequent of a problem that there are scientific papers about it [1]. [1] https://genomebiology.biomedcentral.com/articles/10.1186/s13...

I spent 30 minutes today convincing a Data scientist why she shouldn't use excel to store her interim data and that even an untyped data forms such as csv or json would be a better medium compared to an excel document.
Post reply on HN