11 ms·
Scientists rename genes to stop Microsoft Excel from misreading them as dates (2020)
- pipeline_peak 3y agoThe Onions become so lame I’m just gonna start collecting headlines like this
- k310 3y ago2020 article with this update: Update October 24th, 2023, 11:25AM ET: Microsoft has updated Excel on Windows and macOS, adding a toggle to turn off automatic data conversion. The original version of this article continues below.
- alentred 3y agoThank God they added a toggle and not an on-by-default fuzzy matching where "MARCHF1" would still yield "March 1st". What a pity if the genes had to be renamed again and again, playing hide-and-seek with Excel. I am not usually sarcastic, but can't help it, sorry. Also, I do think this is a very much a possibility.
- ClassyJacket 3y agoHow did we not have that feature in, like... 1993?
- ulrikrasmussen 3y agoI think implicit conversions is the other billion dollar mistake in computing. It just shows up as a subtle source of mistakes in so many places. JavaScript, MySQL, YAML, Excel, PHP, just to make a few off the top of my head, have all caused countless bugs and security exploits due to implicit conversations of data which can be interpreted in multiple ways, and where the system happened to pick the wrong one.
- atoav 3y agoYou can do implicit conversion. In system tou build for yourself and where you are the source of the known input data. Excel is the polar opposite of that.
- lmm 3y agonull is really another special case of the same problem - allowing a nullable type to be implicitly coerced to a nullable one, or rather a general lack of type safety.
- tyleo 3y agoI was also just thinking this… but really only realized it reading the grand-post. Probably the general rule is simply, “you get more bugs when expectations don’t match reality”. Example: 1. (null) This value can’t be null 2. (article) This value won’t be converted into a date 3. (misc) This API can be called frequently
- lmm 3y ago> Probably the general rule is simply, “you get more bugs when expectations don’t match reality”. That's too vague to be useful. You might as well say "you get bugs when you write wrong code". Whereas framing it as a lack of type safety points towards an actionable solution.
- eyelidlessness 3y agoI think this is too strong a take. The general rule, as stated, can be useful as a way to think about systems, interfaces, architecture, etc in broad strokes as a vector for expectations which might fall victim to this pitfall. Being general, it isn’t prescriptive about the solution… but then, type safety isn’t always (and isn’t the only) solution. It’s a way to think about problems where you explicitly scrutinize the expectations they promote and their corresponding realities. You might arrive at type safety as a measure to align those, but you might also consider a variety of other concerns in the same thought process. It’s only unuseful if the problem you’re applying it to is itself vague, or if you’re unprepared to do the thinking involved in understanding a more specific problem and what expectations you’re addressing in solving it.
- KwisatzHaderack 3y agoSo let me get this straight: Scientists would rather rename genes, than to simply use a tool other than MS Excel?
- jskherman 3y agoLol, yes. Scientists, engineers, accountants, businessmen, etc. all use Excel. They say the modern world is built upon spreadsheets (even broken ones that show the wrong data). I forgot who said this though...
- nonrandomstring 3y ago> So let me get this straight: Scientists would rather rename genes, than to simply use a tool other than MS Excel? It's not that they'd rather. They understand that a trillion dollar corporation cannot supply the most basic tool they need for work, despite trying for 40 years. They just can't effect change. The real problem behind it all is that they allow themselves to be bullied. Despite holding multiple PhDs they cannot actually exercise rational choice. The scientists work in an organisation where a suit called Kevin and a middle aged lady in a cardigan called Mavis choose what software they use. Kevin and Mavis are "IT experts". Kevin and Mavis choose Microsoft because they think it is "reputable", and they like the colours, and because HR have had "Microsoft Office" as a mandatory hiring requirement since 1990. Mostly though, they don't want to upset Colin who is head of IT, Colin still thinks Windows XP was the best thing ever and goes a funny shade of red when anyone mentions Linux (or anything else he doesn't understand). Bless him, he's close to retirement anyway. Meanwhile Tom the MD, whose "door is always open" invites the scientists and engineers into a "consultation". They tell him that Microsoft and Google offer 1990s solutions to twenty-first century needs, that their products are unfit for purpose, unstable and insecure too. They even produce a report of all the software they need along with suppliers, budgets and installation guides. Colin has gone a very worrying shade of purple. Six months later the square root of fuck-all has happened. An uncomfortale quiet hangs in the air. Someone nobody ever heard of called Isabella, who actually works for "strategy and brand" in the parent company - and is married to some Microsoft guy - over-rode the whole thing. Tom mumbles something about preferred suppliers and compliance. Kevin and Mavis assure the scientists that Microsoft are going to fix everything in the next update. Colin is restored to a smug pink. Science will have to wait. Big Tech is a complex cultural problem.
- mo_42 3y agoAs a computer scientist this sounds ridiculous. Especially that they restrict themselves to Latin letters and Arabic numbers. I guess many people here will point out that they should use other tools. I think we should help them in any way. They could also prefix every gene with Gn so it cannot be confused with other acronyms (e.g., GnDEC12). It not only breaks parsing by computers but also by humans who might read offensive words into it. Depending on the structure of data this prefix could even be compressed with 0 bits.
- verisimi 3y ago> Especially that they restrict themselves to Latin letters and Arabic numbers. I think this is sensible actually. Certainly better than the inverse, Latin numbers and Arabic letters. I'm not sure why they put it in this way (with the ethnic reference), but they are really just saying about putting it letters and numbers that people use and would be able to spell out to each other. I think your idea of a prefix though is great - simple.
- fabian2k 3y agoGene names are potentially mentioned a lot in a paper, a prefix would be seriously annoying for reading. And which other characters would you want to use here in the names, a restriction to the latin alphabet and numbers seems very reasonable to me?
- bayindirh 3y agoWe, as CS people, think computers are easy. However, for other disciplines, computers are not easy. We can try to (and do) create tools, but it's not as simple as a table editor or a simple CRUD application. Scenarios has many peculiarities and edge cases.
- mnw21cam 3y agoA fair proportion of CS is precisely oriented around the fact that computers are not easy in ways that non-CS people don't realise, and provides methods for quantifying and trying to cope with that. I refer you to https://xkcd.com/2030/ https://xkcd.com/2030/
- helsinkiandrew 3y agoPreviously discussed (621 comments): https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385
- dmz73 3y agoOr they could set the format of the column to text instead of keeping it as general and everything would just work. If these "scientists" are having this problem then they haven't spent any time at all learning how to use the tool they rely on. Can we trust anything these people produce if this is the level of competence they have with Excel?
- JimmyAustin 3y agoA lot of analysis is done using CSVs being pushed in and out of Excel. Doing so strips the formatting. Please understand the workflows being crying “skill issue”.
- knighthack 3y agoSorry, can't understand, and that excuse is unacceptable. It's totally a skill issue, and a failure of it, and a laziness of these 'scientists'. You'd expect scientists - people working to understand the nature of reality - to have some base competency about how they measure reality. Could have at least used a database for things like this; moreover any decent database can often import from CSV and export to CSV as well. Excel is not at fault here; the 'scientists' are.
- eigenket 3y agoHaha, you think scientists have control over what software their employer buys for them to work on. There are probably wonderful places where that is the case, and probably several of those places use something like libreoffice which doesn't do the idiotic data conversions excel does, but they are definitely not the norm.
- cqqxo4zV46cp 3y agoComing up next: people should still use C because writing insecure code is a “skill issue”. Why can’t we as a society make ANYTHING easier without the usual blathering on from the peanut gallery turning it into a question of one’s intelligence?
- bdjsiqoocwk 3y agoIn this thread: People who don't understand that excel is just a tool, which is useful but has flaws, arguing with people who don't understand that experts care about solutions not the tools, in which case it might be ok to compromise something unimportant, like the _name_ of something. What these two sides have in common is the contempt for the actual experts for not building a tool that HN could build but wouldn't know how to use. Peak HN.
- knighthack 3y agoIn this thread: guy pretends to think he's different from the rest, and is able to distinguish the "key issue" unlike everyone else - and of what purportedly doesn't matter, like the name of something.
- bdjsiqoocwk 3y agoI don't have to distinguish anything, I just have to the humility to listen to what the experts say, rather than a bunch of webdevs. EDIT: Also, i'm not a biologist, but I see the same phenomenon in finance where a bunch of low pay webdevs nobodies refuse to make an effort to understand why Excel is popular, push their half backed solutions, and then are confused that nobody uses them, and frustrated when showed for the 10th time the feature set, that they don't really have the attention to detail to implement. Then i come on HN and realize "oh so _this_ is where these people come from".
- wiseowise 3y agoWhat difference does it make if they’re low paid or not? If you want to sound reasonable, at least try to hide your contempt. Better luck next time.
- bdjsiqoocwk 3y agoIt makes a difference because those that solve problems are well paid, whereas those than spend their time pushing their ideals of how things "should be", aren't.
- hulitu 3y ago> Scientists rename genes to stop Microsoft Excel from misreading them as dates I thought they fixed that bug. Oh wait, this is Microsoft. /s
- makeitdouble 3y agoAt this point there must be enough people in the world relying on that behavior that you can't just change it. Perhaps having an option somewhere could be done, but it would sit there among the hundreds of other options that so many people have no idea about. I think changing Excel's default behavior is a social challenge in itself, I'd be having a stomach ulcer for decades if I was Excel's prooduct manager.
- chrisjj 3y agoI've seen no evidence this is a bug.
- hulitu 3y ago> I've seen no evidence this is a bug. Maybe it depends on your use case, but why it converts almost everything to dates ? Why not to numbers ?
- reportgunner 3y agoDates are stored as numbers in Excel. Excel also tries to automatically convert numbers as well.
- mnau 3y agoUsers enter text. That's how it always has been, because they use keyboard. If format is set to General, Excel tries to make the best guess what is inputted and convert it to that value, so things like SUM work. That is very sensible behavior. Your accountant doesn't want to set format and only then enter number. She has a job to do, so just do it for her. Same thing for dates. This guessing algorithm has some edge behavior, but always happens. C# DateTime.TryParse("DEC12") will deduce date is 2012-12-01. Yay? Do you want to keep it as text? Add ' as a first character (it's not considered a part of the string) or format cells to non-General format (e.g. Text) before entering a text.
- martyvis 3y agoTitle should have the (2020) suffix
- cynicalsecurity 3y agoWhy not use LibreOffice instead?
- mnau 3y agoBecause it's garbage. I mean it. Try enter some text to search autofilter 100k rows on LO and Excel. In Excel, it's instant, in LO, I have to go make a coffee. LO 10 seconds, while being frozen (command is likely sync). Excel 2 seconds, while being responsive (command is likely async). There is a saying that you only use 5% of Excel, but everyone uses different 5%. LO is full of stuff like that. Not to mention subtle differences from Excel that (likely) stems from Excel being having it's internal structure aligned with (barely documented) xlsx while LO is aligned with .ods. LO is useful if you just want to make some simple stuff and you don't interact with rest of the world. If you need both, you are only asking for trouble.
- smeej 3y agoThe article has now been edited to note that Excel now offers a toggle to turn this feature off. How long did the entire genetics community struggle with this problem before deciding to rename a bunch of genes, making their own data harder to search, without just...asking Microsoft to add a new toggle for their use case?
- lloydatkinson 3y agoHas anyone ever had any luck asking a large organisation, such as Microsoft, for anything good or meaningful changes? Without a huge controversy or media outcry?
- __s 3y agoI've seen it happen internally over years of sweat & tears A few large customers can move things on specific products if they're able to escalate their support ticket into talking to the right developer
- Retr0id 3y agoGetting media attention is the fast path if you can manage it, and even that took over 3 years, based on the dates mentioned.
- waffleiron 3y agoIt has always been an option, however it's easy to miss it's happening when it happens to a really small subset of genes you are importing into an excel sheet. Most of the time people just don't pay attention, and excel does whatever is useful for most people (i.e. not biologists). Worked in bioinformatics in the past, and I think this is just an issue with people not knowing it did happen. You have many new biologists joining the field every day, people are going to mess up if the standard excel option is conversion.
- DonsDiscountGas 3y agoIt has not always been an option, at least not one that's easy to find.
- oferzelig 3y agoIt's from August 2020 with the latest update almost 6 months ago. Wondering how it got so many upvotes on HN now.
- cqqxo4zV46cp 3y agoBecause people don’t all ingest and retain every event that occurs throughout the universe?
- tambourine_man 3y agoBecause of this: https://news.ycombinator.com/item?id=39823849 https://news.ycombinator.com/item?id=39823849 I went searching for it, found it interesting and so did others.
- samyar 3y agoScience 0 Excel 1
- ijijijjij 3y agoWow... this sound like something that could have happened in the 80s... A bit ridiculous.
- msh 3y agoI dont really get why every tool must be suited for everyone. If excel (which was absolutely not designed for genetic research) is bad for genetic research purposes why blame microsoft. Its like a craft man complaining that a screwdriver is a bad tool if your job is to put in nails.
- eigenket 3y agoWell if the screwdriver sales guy takes your bosses bosses boss and their spouse out for a nice dinner, mysteriously leading to your org purchasing screwdrivers for everyone who is asking for a hammer to do their work then maybe the hypothetical craftsperson might start complaining. A ridiculously large number of people have excel installations and exactly zero budget to get access to a less shit tool.
- msh 3y agoThen blame their employer (especially if it's a billion dollar medical company) if they are so easily lead a stray.
- M95D 3y agoI'm really annoyed that even Open/LibreOffice copied this crap behaviour. I try to avoid spreadsheets. I won't use them unless there's calculations to be done. When I do use them, the first thing when I create a new sheet is to select all cells and set it to text. It's probably a bad habit, but it's the only way to avoid future frustration and mistakes.
- stef25 3y agoIsn't it possible to just set the format of the cell to plain text ? Whoever works with genetic data in Excel should know how to do this, if correct.
- ivanche 3y agoWhenever a question contains "just", the answer is 99.9% "no".
- mttjj 3y agoCan't you just round up that statistic to 100%?
- LikesPwsh 3y agoFor your workaround you'd need to open the CSV in a text editor, set Excel types in a blank sheet, then paste. Not a problem for someone who understands the issue, but it is a slow/confusing way to open files. The real problem is the default behaviour of double-clicking a CSV in Explorer and destroying the contents.
- moi2388 3y agoKeep using excel, access, js and Python. It’ll be fine. >.>
- emporas 3y agoOne good solution rather than Excel is org-mode. See the original Google talk by org-mode creator [1]. At some point 30 minutes or so, he explains how to use org-mode as a spreadsheet. Not so powerful as Excel, even though missing features might be trivially added. I haven't tried it myself but i want to really dive into it's functionality. Org-mode is really great at many things, spreadsheets might as well be one more. [1]https://www.youtube.com/watch?v=oJTwQvgfgMM https://www.youtube.com/watch?v=oJTwQvgfgMM
- neglesaks 3y agoI need to introduce my customers using Excel to the typecasting concept. :o>
- CoastalCoder 3y ago> I need to introduce my customers using Excel to the typecasting concept. I misread this as "... the typesetting concept.". I immediately feared someone adding LaTeX support to Excel cells. But now I'm thinking "hmmmm..."
- deleted 3y ago[deleted]
- ChrisArchitect 3y ago(2020) More discussion then: https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385
- todorov84 3y agoOh, come on. Just format the range as text before pasting or importing the data (right-click -> format cells -> category = text). Text-formatted ranges don't convert input data. The article also doesn't help saying that there is a toggle for implicit conversions. You want text, you preformat as text. Just learn to use the damn thing before you bash it. Especially if it's your daily driver.
- __lbracket__ 3y agoTo be expected from people who ended up in bio