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

61–70 of 518 posts

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

#61

Post by catalogia »

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.

Excel is from an era when programs still catered to power users. Tools were made to have learning curves, ideally not particularly steep curves, but curves nevertheless. It wasn't expected that users would hit the app running, intuiting everything there was to know about the program in their first minute of using it.

The result is a rich deep program that users can grow into, rather than a shallow trivial program that optimizes for the noob experience and leaves power users out in the cold.

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

#62

Post by airstrike »

numpad0 wrote: post #5

Excel’s problem is it destroys the original keystrokes. Anyone know why? It makes little sense to me.

Because most of the time you actually want it to be a date and those are stored in the spreadsheet as integers

You can add a single quote before any value to prevent Excel from autoformatting

Or just turn off autoformatting entirely

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

#63

Post by aden1ne »

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...

This ignores the reality that one will ultimately have to interact with people who have no understanding of any of these tools. Unless you happen to work in a pure computational biology group, one _will_ have to interact with lab workers, biologists with no training (or understanding of) in R or python, doctors, etc. All these people will know excel.

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

#64

Post by ppod »

jordigh wrote: post #33
azalemeth wrote: post #23

Earlier quoted context omitted.

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

Excel is not fundamentally suited, but it's fundamentally used. I wish you luck, but I long ago declared defeat against Excel. I can't prevent everyone from using it. I have come to accept that whatever Excel does is what I have to live with, because someone, somewhere out there hasn't heard the anti-Excel gospel yet.

Quantitative social science (which maybe a lot of comp bio people might regard as a "softer" science) has really made huge strides in moving this kind of analysis to R. Of course one person can't change it, but those with high status in the field can make a difference by leading the way and setting standards.

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

#65

Post by pas »

appleflaxen wrote: post #37
azalemeth wrote: post #23

Earlier quoted context omitted.

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

> Excel is fundamentally not suited to analysing *omics data you are 100% correct. and yet it will continue to happen.

Yes, and? It should be a simple checklist item for any scientific journal to ask the author whether they used excel or not. If they say yes, reject the paper unless they can show their work hasn't been affected negatively by using excel.

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

#66

Post by donatj »

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…

So much this. I work in educational software, and have a little write up about the troubles we’ve had with schools “fixing up” CSVs from their student information systems before sending them to us. The simple act of opening the CSV and hitting save loses data (Microsoft actually fixed some of the problem very recently). That said, they will use Excel, it’s not in my control.

I received dozens of comments “Just use Open Office / Libre Office”. I am but a man, I cannot change the world. I forgive that which I cannot control. We work with tens of thousands of schools. Getting all of their administrators to install Open Office, and thousands of administrative assistants to remember to use it rather than Excel is simply an impossibility.

- https://donatstudios.com/CSV-An-Encoding-Nightmare

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

#68

Post by catalogia »

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?

> Why in the world is excel the application of choice?

Because if it weren't for Excel, most Excel users would have to hire programmers. (And Gnumeric is very obscure, how many non-programmers have heard of it?)

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

#69

Post by dr_orpheus »

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…

Having Excel be the default for .csv files has caused me problems in the past as well with Excel automatically changing data when you open it. The unfortunate thing I have come across multiple times is a csv file that has some values with a lot of significant figures, i.e.

123456789876, 987654321234

After opening in Excel it will convert these to:

1.23E+11, 9.87E+11

Then resaving the file (as a csv) will store the values in scientific notation and lose all of the digits.

Post reply on HN