Scientists rename human genes to stop MS Excel from misreading them as dates • Hacker News Board

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

#21

Post by Cactus2018 » Thu, Aug 06, 2020, 1:38 PM UTC

Shivetya wrote: post #10

It is just so fun when you have @ as a lead character for fields. You can do DATA-IMPORT from CSV and it is fine but if you just double click load a CSV from explorer it tries to interpret the data as a formula and randomly loses the @ I have not check myself the full list of special characters that cannot be loaded CSV style from Explorer but one should expect consistency with DATA-IMPORT functionality

Two more examples.

In a CSV with zip codes, Excel drops the leading zero: Boston, 02114.

A CSV with text ranges: 1-10, 11-20, 21-30... becomes 10-Jan and 20-Nov!

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

#22

Post by ocdtrekkie » Thu, Aug 06, 2020, 1:44 PM UTC

Another reason why not to "wait for an update for Excel" is that one of the perks of Excel is it's very long-lived data formats. People may be using Excel 2003 to work with you on a file you created in Excel 2019. You have to go all the way back to Excel 2000 to lose the ability to work on modern documents.

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

#23

Post by azalemeth » Thu, Aug 06, 2020, 1:44 PM UTC

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 you see wrong things in the world, do you accept them as they are, or try -- however ineffectually -- to force change for the better? I for one bang the drum into the wind and try to get biochemists off it. I teach people to be very sceptical of excel in my stats courses, for example (aside from some showstopping bugs and downright dangerous defaults, its RNG is particularly crap).

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

#24

Post by leecarraher » Thu, Aug 06, 2020, 1:46 PM UTC

Final Jeopardy : Trebeck: You know what, how about you just write down a number, any number at all. Could be a 1 or a 2, perhaps 3... and excel you answered; A smiley face emoji, simply stunning.

Excel is no longer motivated by the original intention of a spreadsheet, and now caters to the lowest common denominator, a piece of graph paper. As such MS has shifted focus from doing calculation to text and graphics layout tool. white text copied from a terminal : white text white background you got it! comma separated numbers : default a long string with commas in it want a plot : it is in the insert menu for some reason, since plots and numbers are no longer excels raison d'être

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

#25

Post by sam_lowry_ » Thu, Aug 06, 2020, 1:48 PM UTC

treeman79 wrote: post #18

lunchladydoris wrote: post #2

Or perhaps stop using Excel?

Excel is amazing at what it does. I had a Manager that spent an entire week compiling a report on how much times people spent on tasks. He was very proud as he was showing me this report. I asked him why he didn’t use a pivot table. While he was asking what that was, I spent two minutes on the same data that he had and reproduced his report. I had a friend and patron for life after that. I was involved in many major…

These Excel people feel like they know something while they are just nefarious bugs ;-(

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

#26

Post by LatteLazy » Thu, Aug 06, 2020, 1:49 PM UTC

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

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

#27

Post by Legogris » Thu, Aug 06, 2020, 1:50 PM UTC

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…

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

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

#28

Post by unnouinceput » Thu, Aug 06, 2020, 1:51 PM UTC

Quote: "There’s no easy fix, either. Excel doesn’t offer the option to turn off this auto-formatting..."

What kind of loopy Excel variant the article's author is using? It's right there in settings, you can easy stop auto-formatting. Yes, default installation has it on, but it can be turned off.

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

#29

Post by proactivesvcs » Thu, Aug 06, 2020, 1:51 PM UTC

numpad0 wrote: post #5

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

In this case, because it's much faster to enter data such as dates by entering the shorthand - e.g. 6/8 - than have to type 06/08/2020. The same reason it's very helpful to type "500" into a currency cell and have it convert to "£500.00", or the many other sorts of autotype.

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

#30

Post by qayxc » Thu, Aug 06, 2020, 1:53 PM UTC

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.