13 ms·

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

• lunchladydoris 6y ago
  Or perhaps stop using Excel?
  • chapium 6y ago
    Excel is pretty efficient at ad-hoc text processing. You get real time feedback as you change rules and there are lots of handy functions to help you along. Its like a tabbed portable jupyter notebook.
    • kaonwarb 6y ago
      Love this analogy. Yes, Excel is error-prone at its core. It's also the most widely-used and accessible IDE yet created. Those statements are correlated.
  • DethNinja 6y ago
    What’s the alternative?
    • hutzlibu 6y ago
      Well, everybody should set up their own, custom script powered favourite DB of course. Ok, seriously, it would be nice to te able to recommend a alternative. I heard LibreOffice Calc is not really equivalent?
      • welterde 6y ago
        python+pandas [1] is probably quite an easy choice for most common things people would use excel for. And being in the python ecosystem offers lots of libraries for statistics, machine learning, signal analysis, plotting etc. [1] https://pandas.pydata.org/docs/user_guide/10min.html https://pandas.pydata.org/docs/user_guide/10min.html
        • vxNsr 6y ago
          People like real time feedback it's why word is more popular than tex, all these tech folks pushing code solutions to wysiwyg software issues are missing the point.
          • welterde 6y ago
            Ipython/jupyter notebooks fill that niche quite nicely.
    • proactivesvcs 6y ago
      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.
    • dade_ 6y ago
      I use LibreOffice Calc for most of my work. It works just fine. The question I have is not about using spreadsheets, but why Excel? It takes way too long to load and the UI gets worse with every iteration. Most people don’t even use 99% of the features in Excel (Find and Replace amazes some people). It is headed on the same trajectory as the abomination of diagramming software known as Visio.
  • treeman79 6y ago
    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 decisions for a fortune 50. All because I could work excel.
    • sam_lowry_ 6y ago
      These Excel people feel like they know something while they are just nefarious bugs ;-(
  • mobilio 6y ago
    This happens before, and will happen again: https://qz.com/119578/damn-you-excel-spreadsheets-jp-morgan-edition/ https://qz.com/119578/damn-you-excel-spreadsheets-jp-morgan-...
• lvturner 6y ago
  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?
  • hairofadog 6y ago
    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 spreadsheet app. Edit: or maybe Sublime Text is more what I mean. In any case I’m hoping someone will pop in and say, “well why aren’t you Snappets!?” and then I’ll go buy a copy of Snappets.
    • Legogris 6y ago
      Not exactly what you're asking for, but what about libreoffice?
      • hairofadog 6y ago
        It’s been a while since I fooled around with that. I’ll give it another look.
    • bransonf 6y ago
      See this Exchange thread [0] I personally use Table Tool for the most basic editing/collection. [0] https://apple.stackexchange.com/questions/15946/free-csv-text-file-editor-for-mac-os-x https://apple.stackexchange.com/questions/15946/free-csv-tex...
    • brisance 6y ago
      Have you taken a look at Wizard? https://www.wizardmac.com/support.html https://www.wizardmac.com/support.html
    • 6y ago
• yummypaint 6y ago
  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?
  • zapdrive 6y ago
    Maybe you should ditch your Pentium 1 and buy a newer computer?
    • mellow2020 6y ago
      If the same machine can do it without hanging with better coded software, the hardware obviously isn't the problem.
  • NikolaeVarius 6y ago
    Excel is the application of choice because the world runs on Excel. Get with the times.
  • curiousllama 6y ago
    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"
  • airstrike 6y ago
    I regularly use 10MB+ files with circular calculations turned on and it's no biggie most of the time
  • catalogia 6y ago
    > 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?)
• numpad0 6y ago
  Excel’s problem is it destroys the original keystrokes. Anyone know why? It makes little sense to me.
  • dkarl 6y ago
    Because usability is measured by the naive expectations of the least sophisticated users.
    • numpad0 6y ago
      But haven’t you seen the least sophisticated users trying to type in the same sequence until it sticks?
  • proactivesvcs 6y ago
    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.
  • airstrike 6y ago
    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
• jordigh 6y ago
  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 against fundamental forces of nature. This is just an example of that fundamental force winning.
  • pratio 6y ago
    True in every sense. We shifted from google sheets to O365, there's just no way to go around excel. Its efficient and every one from a novice to expert can find their way around it.
    • pmarreck 6y ago
      Pray tell, why did you switch from Sheets?
      • pratio 6y ago
        The dataset was growing and sheets were slowing down. Excel installed locally on a machine with good hardware is a pleasure to work with. As the team grew more and more people were asking for office licenses and like i said, we have colleagues who've been using excel for ages and they like to have 2-3 files open on multiple screens and work between files using references. It's just not as fast as using excel locally. I'd like to add that we also had an issue with our clients using only excel and there were issues with calculations not working, not so often but with enough people making noise our management decided to get the subscription. I've been using sheets for years even as a student when i couldn't afford an office license.
  • azalemeth 6y ago
    > " 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).
• Duller-Finite 6y ago
  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/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
  • Dumblydorr 6y ago
    I had to use Excel for 100% of my publications and posters in medical services research. Either the data is in Excel or Excel is a tidy place to put data dictionaries. While I'd love to use only R, most of my collaborators wouldn't be able to use it, it's niche, whereas Excel is the lingua franca of data analysis.
  • xkgt 6y ago
    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.
  • aden1ne 6y ago
    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.
    • Duller-Finite 6y ago
      That's why I'm in favor of this change in nomenclature.
      • otherme123 6y ago
        IMO the change is good, but is a case of detected vs undetected. Recently I was working with some colleagues, being I the computer savvy and them the lab people. I send them some data in CSV, that when opened in Excel turned 123.456 into 123456 (it was a problem with locales, some people using "," as decimal and some using "."). We noticed because the values should be between 0 and 1000. But what if the column could be between 0 and 1000000? A small quantity of numbers bumped up by a factor of 3 could fly under the radar, and distort further measurements. And the error is undetectable forever once published. I like it better the programming language approach: look, this is how you write a string, this is a char, this is a float and this an integer. "2020-08-04" is a string until you ask me to turn it into a date. "SEPT1" is a string, and you are going to do quite the gymnastics to make me understand it as "date(2020, 9, 1)". Do you like "," or "." as thousands? Then we first turn the number into a string and then format, but the original number is kept.
• jaclaz 6y ago
  Or perhaps prepending them when typing with either a single quote, apex or double quote (left/center/right align AND consider as Text), like all the rest of Excel users have done for the last 25+ years?
  • psychometry 6y ago
    You don't understand the problem. Read the article.
    • proactivesvcs 6y ago
      I read the article and parent is correct in part. However, equally the article touches on the fact that many people may use a spreadsheet and not all will be correctly trained to use their complex tools properly, in order to ensure data integrity. I consider this a failing not in software or Excel, but in education and professional standards. Scientists can be expected to understand and use their tools correctly, since not doing so will taint their data.
      • conductr 6y ago
        Is the simple renaming of 27 genes a good solve? I think it is. Any time you expect people to be trained, you’re planning for failure. Even a trained person can make a mistake or forget to do the manual steps. This eliminates the possibility.
        • proactivesvcs 6y ago
          I think it's the pragmatic approach, yes. I'm disappointed that the article (and so many of the comments here) are not addressing the root cause. The worst part is that not all of the people using it need to be trained - data validity enforcement ought to solve this outright.
    • jaclaz 6y ago
      >You don't understand the problem. Read the article. Maybe I don't understand the problem, but I did read the article. Care to explain what I don't understand? > so when a user inputs a gene’s alphanumeric symbol into a spreadsheet, like MARCH1 — short for “Membrane Associated Ring-CH-Type Finger 1” — Excel converts that into a date: 1-Mar. I can assure you that if you prepend what you type with a single quote, or an apex or a double quote, what you type will be interpreted a text in Excel, respectively left, center and right aligned. Even a number, i.e. "45 will result in 45(right aligned), and a small green triangle in top left corner and if you hover on it with the mouse it will pop-up something to the effect of "number formatted as text".
• Shivetya 6y ago
  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
  • Cactus2018 6y ago
    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!
    • qayxc 6y ago
      That's because CSV is an untyped data format. Why don't people use the import options available to them? You can select the precise data type of each column if you know the format anyway. While the default choices that Excel makes are questionable at times, they're both known and can easily be overridden.
• engineer_22 6y ago
  Human language is more flexible than computer language. We send a few emails, agree on a new word, bingo bango problem solved. Easier than everyone learning a new computer program, easier than teaching everyone how to avoid the errors in Excel, just send a memo to all involved, and those who miss the memo will slowly catch on anyway. MARCH1 -> MARCHF1 seems like a good change and will not cause any confusion.
• Cactus2018 6y ago
  In a previous version of MS Excel, after opening a CSV file, Excel would silently write the 'interpreted' version to disk. So much trouble, simply from previewing a CSV.
  • ImaCake 6y ago
    I have had excel change date formats on me several times because of this "feature". I noticed it no longer does this sometime in the past year.
• ocdtrekkie 6y ago
  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.
• leecarraher 6y ago
  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
  • jkaptur 6y ago
    The date parsing being discussed has been the behavior for at least 20 years, probably more like 30. Your comment about graph paper echoes a comment from a former Excel PM: "The gridlines are the most important feature of Excel, not recalc." https://www.joelonsoftware.com/2012/01/06/how-trello-is-different/ https://www.joelonsoftware.com/2012/01/06/how-trello-is-diff...
    • leecarraher 6y ago
      Nice, well there you go.
  • ubermonkey 6y ago
    MSFT did the same thing with Project when they introduced something called "Manually Scheduled Tasks." Project is fundamentally a critical path scheduling tool, or at least was. Task A must finish before Task B, which must complete before Task C. If A is delayed, then that delay pushes B and C out, too. This is what it's FOR, more or less. Manually scheduled tasks don't move. They're set with whatever dates you give them, and do not move in response to delays or whatnot from predecessor tasks. People wanted this because some (dumb) people insisted that "well, that task CANT move because it has to be done by then!" This is akin to asking for the arithmetic engine to be turned off in Excel, because by golly you really need 2 and 2 to sum to 17.5.
    • 6y ago
• LatteLazy 6y ago
  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...)
  • qayxc 6y ago
    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.
    • LatteLazy 6y ago
      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...
    • pwinnski 6y ago
      This is not auto-formatting, it's deeper than that. It is not actually possible to disable Excel's date recognition, although you can re-change the format of a cell after the fact.
    • catalogia 6y ago
      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.
• unnouinceput 6y ago
  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.
  • mplanchard 6y ago
    From the article: > Even then, a scientist might fix their own data, but as soon as someone else opens the same spreadsheet in Excel without thinking, errors will be introduced all over again.
    • tomiantenna 6y ago
      Ah, the ole "well it worked fine on MY machine". A classic.
  • conductr 6y ago
    Also you can single quote ‘MARCH1 to keep it as string. This is safer as when you share the file you don’t know if the next person has the same settings change you described. Either way, it’s only 27 genes they had to rename. Seems like a good choice on their part to just rename them. Hopefully they’re doing this check during the naming stage for new genes.
    • taejo 6y ago
      Now save that file as CSV (maybe because you want to process it with some non-Excel tool) and re-open it. Mar-1.
      • conductr 6y ago
        Touché. I do everything in Excel file formats but I’m also not a scientist.
  • sseagull 6y ago
    Can you explain where? I just booted up a relatively clean copy of Excel 2019 and can't find it after a couple minutes. I see a few autoformatting options, but nothing corresponding to dates. The only options I see are similar to this: https://www.journalofaccountancy.com/issues/2016/dec/how-to-turn-off-excel-auto-format.html https://www.journalofaccountancy.com/issues/2016/dec/how-to-...
• t-c-h 6y ago
  As a bioinformatician I'm not too fond of this shift. Software should be sculpted around our needs, not the other way around. It's basically submitting to the fact that we've stubbed our toes hundreds of times to the exact same rock and never learned. But then again, I use Excel rarely, usually only at the very end of some analysis (even then I prefer R/Python libraries for visuals). So I do have sympathy for wet-lab researchers who rely heavily on Excel.
• lordnacho 6y ago
  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.
  • jrott 6y ago
    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.
    • lordnacho 6y ago
      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.
      • jrott 6y ago
        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.
    • dgb23 6y ago
      > From a coder perspective I've only seen excel workbooks that are bad or terrifying. I think this might be a type of bias: The Excel sheets I usually look at as a programmer are from customers who want me to write an application for them. Typically because the conventional means (Excel sheets, directory structures, emails etc.) become unmaintainable. So we end up seeing the stuff that is bad for w/e reason. Often a combination of complexity, inconsistency, usability and sheer mass.
• ynodir 6y ago
  Havent read the article, but come on. Tools should serve us, not the other way around. It'd be enough just to change the affected cells' type.
  • jmkjaer 6y ago
    > Havent read the article Please do. The article has a paragraph that addresses this.
• curiousllama 6y ago
  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?
  • bronzeage 6y ago
    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.
• Brett_S 6y ago
  If the scientists had asked someone who knew Excel well, then they would have been told to prevent autocorrect from running enter ’MARCH1 with the apostrophe at the start.
  • mrunkel 6y ago
    This. Why does nobody know this?
• angel_j 6y ago
  There must be a net benefit to using this software, right? Otherwise somebody in charge would make the decision to use something else, right? It’s not like they are forced to use MS excel, right?
• tomp 6y ago
  > For example, HECA used to have the gene name ‘headcase homolog (Drosophila),’ named after the equivalent gene in fruit fly, but we changed it to ‘hdc homolog, cell cycle regulator’ to avoid potential offense.” Ugh... everything is politics now.
  • krastanov 6y ago
    As the sentence before the one you quoted explains, this is not done for politics. It is done because clinicians have to be taken seriously by the parents of the child having a mutation in that gene. Saying "your child has a headcase mutation" ends up causing defensive reactions instead of discussing treatment. Maybe that is an irrational reaction on the parents' part, but not everyone is super rational the first moment they learn their child has an illness.
  • _petronius 6y ago
    As Thomas Mann said, everything is politics. I would add that has always been the case. You can get frustrated about that fact, or you can use it as an opportunity for learning about how and why that is so, and gain a deeper understanding of the tightly linked systems of power relationships all around you.
• takluyver 6y ago
  Programmers love to complain about Excel, but we happily use YAML, which has essentially the same footgun: certain strings (like 'on') need quoting if you don't want it to interpret them as something else.
  • fabian2k 6y ago
    We programmers also complain a lot about YAML, though maybe not enough and not as much as about Excel. But some YAML footguns like the country code for Norway being interpreted as a boolean are reasonably famous, and I think widely regarded as a bad idea.
    • takluyver 6y ago
      To be fair, there are few things programmers don't complain about. But YAML still seems to be very popular as a configuration format for new tools, e.g. CI services.
      • 3pt14159 6y ago
        I wish everyone would just switch to TOML. It's sane and readable.
        • t-writescode 6y ago
          Or json!!! Why did we leave json???
          • RcouF1uZ4gsC 6y ago
            No comments in standard json.
            • 3pt14159 6y ago
              And you have to choose between readability and whitespace. Doesn't matter for most usecases, but you can't blindly write it out. You have to choose ahead of time: 1. Potentially shoot yourself in the foot with tons of extra whitespace. 2. Unreadable mess. TOML is better. You can choose whitespace if you want, but you can always go back to dot notation. Though I (sadly) agree JSON is better than YAML. One of the few times I've changed my mind from A to B and then back to A in tech.
• rkachowski 6y ago
  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.
  • ep103 6y ago
    My favorite is still that Excel can't handle dates before 1900
  • mmcgaha 6y ago
    I hate to sound like a salty old IT guy, but here we go. It is not the fault of Excel that people are using it wrong. They have the ability to import the data as text but they skip that step all together. If the user does not say up front what the column is, Excel has to guess. If Excel didn't try to guess, someone would be making a comment on how bad usability is when an obvious date field was getting interpreted as text.
    • WorldMaker 6y ago
      It's also behavior that goes back to the Ancient Times and predecessors such as Visicalc and Lotus 1-2-3. Even ancient ones will tell you if you need to enter a thing and it has to be text and only text precede it with a quote mark, ie 'MARCH1, just as you would precede a formula with =. It's Spreadsheet 101 knowledge dating back many decades. The clickbait headline is fun, but the real headline is more like "Scientists find it easier to rename things than learn the basics of data entry in the tools they use".
      • jackvalentine 6y ago
        > It's Spreadsheet 101 knowledge dating back many decades. Astounding, I've literally never heard of this in the 20 years or so I've been using spreadsheets. I'll be using it from now on!
• mnw21cam 6y ago
  The problem is it isn't just dates. If you load a list of genomic variants (read: mutations) into Excel, then the standard way to describe whether a person has the variant or not is to use "0/0" for no, "0/1" for yes heterozygous, and "1/1" for yes homozygous. Guess what gets auto-converted into the first of January.
• mattmar96 6y ago
  If this kind of thing amuses you, check out the book Humble Pi by Matt Parker. Its a collection of stories about maths errors. Lo and behold, most of them happen in Excel.
• DarkWiiPlayer 6y ago
  Reading this just makes me incredibly sad. This feels so dumb and backwards... Why do people even still use excell at all?
  • epistasis 6y ago
    What are the alternatives? I tend to use "less -Sx 20" to preview data files but try explaining that to a non-CLI user on Windows.
  • jpindar 6y ago
    Because their manager wants the data in Excel, because their managers used Excel, because THEIR managers used Excel. It's managers all the way down.
• glofish 6y ago
  Infuriatingly, the paper announcing the new guidelines of renaming genes, a work of fundamental importance to all scientists in the world, cannot be read without an expensive subscription to the journal. https://www.nature.com/articles/s41588-020-0669-3 https://www.nature.com/articles/s41588-020-0669-3 Thanks science (sarcasm!)
  • rolph 6y ago
    copy paste the DOI into scihub
    • LordDragonfang 6y ago
      That's entirely missing the point.
      • dj_mc_merlin 6y ago
        it works
        • aaron695 6y ago
          Just like Excel
          • diarrhea 6y ago
            Until it doesn't...
• racl101 6y ago
  Well, it's funny to know that even the people doing the most cutting edge stuff are also getting fucked in the rear by that piece of shit software Excel. Seriously, I hardly trust the program anymore. I always need to get the truth from Python and Pandas.
• alistairSH 6y ago
  Ugh. Why is "MARCH1" interpreted as a date at all? Does anybody use that as shorthand for March 1?
• Gatsky 6y ago
  Ah, it’s a shame they are doing this. Finding garbled gene names is a quick way to pick a poor quality paper when reviewing.
• snow_mac 6y ago
  Why Doesn't Excel allow you to change date formatting? or column formatting?
  • emteycz 6y ago
    It does.
• jtdev 6y ago
  Excel is the 2020s technological progress stunting equivalent of the fax machine.
• jbaber 6y ago
  I don't see as pitiful that these non-technical* people can only use Excel. I see as glorious that I'm able to get something close to real database tables out of non-technical people as long as they're in Excel. I once populated a pretty sophisticated database by giving a bunch of computer semi-literate people Excel tables with only headers and simple instructions. They respected foreign key constraints, etc. that I could never have directly explained. *in the software engineering way
• csours 6y ago
  Excel also clobbers long numbers, which has caused all manner of confusion for serial number audits. To prevent this, set your whole sheet to Text before any other steps.
• pkphilip 6y ago
  Couldn't they have just used a prefix. Eg: G_MARCH1 instead of renaming the whole set?
• mcv 6y ago
  > "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 if Excel is such a poor tool for so many things?
  • acid__ 6y ago
    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.
    • reportgunner 6y ago
      From my point of view Excel hits a sweet spot between 'very simple tasks' and 'very complex tasks'.
      • _emacsomancer_ 6y ago
        The sweet spot being "too complicated for simple tasks" and "not sophisticated enough for complex tasks"?
        • BbzzbB 6y ago
          >"too complicated for simple tasks" How can Excel possibly be too complicated for simple tasks? It is pretty much as straightforward as it goes when it comes to grid-file viewing and editing. You can show it to anyone from a high-schooler to a 60 year old (with minimal experience on computers) colleagues and they will figure it out rather easily, good luck teaching Python/Pandas to the latter. >"not sophisticated enough for complex tasks" Not sure how that works either really. Between formulas and VBA macros, people have and are making tools complex enough they have no business to be an Excel, and yet they are even if it isn't the best tool for it. Once you go past that point, Excel isn't even in the conversation nor does it pretend to be able to. It has issues, and people playing or working with complex (or simple) data would be better served to learn programmatical tools, but until they do Excel will serve them well as long as they stay wary of basic quirks.
• raphlinus 6y ago
  I know jokes are frowned upon in this forum, but I will take a chance on this one because I think it is relevant and is food for thought: A Venn diagram. Left circle is "Excel", right circle is "Incel", intersection is "incorrectly assuming something is a date".
• danso 6y ago
  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 https://www.nature.com/articles/s41588-020-0669-3 [1] https://www.genenames.org/about/guidelines/ https://www.genenames.org/about/guidelines/ [2] https://pubmed.ncbi.nlm.nih.gov/11944974/ https://pubmed.ncbi.nlm.nih.gov/11944974/
• bronzeage 6y ago
  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.
• arkanciscan 6y ago
  I wish we could rename addresses with "drive" in them so that Instapaper and Pocket stop reading the abbreviation as "Doctor". "There was a shooting today on The 800 block of MLK Doctor"
• vikramkr 6y ago
  A lot of people are attacking excel in this thread, just remember to give a fair share of the blame to people naming genes as well. These are meaningless names and changing them is frankly easier than changing a feature in excel (that the finance folk probably don't want changed). And its a good excuse to clear up some of the egregiously silly disease related names as well so we don't tell parents their kid is suffering from a debilitating mutation in luke-Skywalker-like upside-down cantaloupe 12b or whatever.
  • rolph 6y ago
    no these are not meaningless names, there is undue confusication in the case of the more contemporary names such as [Sonichedgehog] these are not meaningless lables https://en.wikipedia.org/wiki/Sonic_hedgehog https://en.wikipedia.org/wiki/Sonic_hedgehog mentioned eslewhere in this thread is a nomenclature that allows one to easily find notes referring to the gene from your research library. It was a new cadre of young upcoming scientists that decided to break with tradition and use something familiar to lable genes according to game characters or pop icons.
    • vikramkr 6y ago
      They're tags and you end up with multiple names for the same gene. They might as well be replaced with an arbitrary string of numbers- those are equally easy to do a quick Google scholar or pubmed search with. You don't lose anything of value by changing the name. In chemistry amd organic chemistry, the proper IUPAC name of the compound also tells you its structure. It tells you what the compound is in a real physical sense. If you go to someone thats never heard of a compound d and you give them the full IUPAC name, they can draw it. You can't change those names without losing meaning. You go to someone who's never hears of the hedgehog signaling pathway and ask them what SHH is, they're not gonna be able to give you much.
      • rolph 6y ago
        this does not negate the fact that these tags or lables actually do have meaning they are not arbitrarily made up. if you do not have any understanding of rust and you read the source code it seems like meaningless tags but if you understand the idea of syntax you can see there is some underlying principle to the combinations of characters. being unfamiliar with the scheme doesnt make it meaningless hash. the failure is not representation it is interpretation. if we want to talk about absolutes the universal way to identify a gene is with its locus or loci depending on the nature of the gene, before we get there we use "tags" lables indexes to allow quick access to a particular character in a local store of data. the whole thing about revising the standards of a genes identifying name is about getting the greatest benefit quickly with the least effort so we can get back to science and away from blaming the tools for the lack of craftsmanship.
• yters 6y ago
  Why not an excel plugin to fix the problem?
• nesarkvechnep 6y ago
  https://thedailywtf.com/articles/another-immovable-spreadsheet https://thedailywtf.com/articles/another-immovable-spreadshe...
• alkonaut 6y ago
  Automatic type conversion is the root of so much pain. It doesn’t just apply to C# or javascript apparently.
• komali2 6y ago
  In excel, can you set the a column to not format to dates? I think you can do that in google docs for example. Why don't they do that?
  • LatteLazy 6y ago
    It's actually worse. If you expressly set the format of a column to text, then put 01/01 in it, you still get Jan-01 and a field value of 46000ish.
    • komali2 6y ago
      That's horrible UX - why on earth is it that way?
• rolph 6y ago
  when i was undergrad there was an academically priced package for about 500$ you get the entire suite of word excel access powerpoint bells whistles and nice rugged carrier box for all the tomes. i dont groom well i used it until taking an assistants position and we used ...lotus, symphony,and norton utilities and some funky in house coded version of a Dbase, and IC4 [some inventory control utility] Excel was nowhere in sight, and that was thirty years ago. If someone wants a data set i give them a .csv it is then thier fault if they plug it into excel.
• 24gttghh 6y ago
  Just change the data type of the column in question?? In the article it even mentions this and links to how to change it: >Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns. https://www.youtube.com/watch?v=SppKiKIdCkI&feature=youtu.be https://www.youtube.com/watch?v=SppKiKIdCkI&feature=youtu.be
• zw123456 6y ago
  There is a super simple work around for this and it is all over the web if you search for it. Open a blank sheet, go to data, click on From Text, that allows you to import data identifying the type for each column. Yes, it is a couple more clicks but for 99% of people the auto-formatting is probably nice. I have shown this to people a million times at work and they are always amazed. But honestly a quick google search provides the solution.
• patgrdj 6y ago
  So all that noise for people that don't know to change the data type of a column?
• jpeloquin 6y ago
  Most of the reactions seem fall into three categories: 1. "You (the individual) should stop using Excel". Good advice. As the article mentions, though, if you ever send a tabular data file to someone else to edit, there's a fair chance they will open it in Excel and corrupt the data. Excel's CSV import/export cycle has even more traps than using xlsx consistently, so in some sense individual avoidance of Excel makes the problem worse. You could send SQLite files or somesuch so your colleagues can't work with your data, I suppose. 2. "You (all scientists / whatever non-programmer group) should stop using Excel for data analysis". True, but getting everyone to change is hard. Both top-down (thou shalt not use Excel!) and market-driven (Airtable! LibreOffice!) haven't worked so far. Hopefully there will be more progress on this front. 3. "Scientists should learn to use their tools". Even if a user knows all the scenarios in which Excel corrupts data, it cannot in general be stopped from doing so. If you set column types up front, type every value manually (no copy-paste) & save and check on each edit, and then never re-save the file again or let anyone else re-save it, you're safe. Probably. I sort of expect a bunch of replies saying this isn't safe at all. But if the file is edited, you need to check the whole thing. Eventually an error will slip through. The only way I've found to safely work with Excel files, or files that might be edited in Excel, is to put the data files under version control and always check the diffs. Often someone else's import/export cycle will change the quoting style on the whole file. In this case, the work needs to be repeated so the diffs are clean and the changes can be verified. This is a necessary but very obnoxious part of this process. Version control targeted at non-programmers and non-text files would help a lot. There are also tools to to flag potential data corruption errors [1], but error detection in the absence of the original data won't be perfect. And if it is manually run, it won't be run consistently. Most people don't have continuous integration pipelines for their data. Excel is very powerful and has a lot of potential to improve non-programmer productivity. It is unfortunate that avoiding data corruption is such a minefield. The situation could be significantly improved by anyone with a decent product and a great marketing strategy. [1] http://maplab.imppc.org/truke/ http://maplab.imppc.org/truke/
• justinsaccount 6y ago
  No one knows how to use excel. https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
• superjan 6y ago
  Tangent: I once tried to help a friend (non-coder) with his slow excel sheet. He was using string search in excel to match virus DNA fragments. As you’d expect, it was way more complicated than neccesary, and likely buggy. I searched around for something better. But if you cant code, its a really big step to ditch your excel and learn a Programming language. But there is no such thing. It’s understandable that people stick with what they know, but the results are awful.
• Metacelsus 6y ago
  This happened to me! Someone sent me a spreadsheet where OCT4 was changed to "October 4"
• phendrenad2 6y ago
  If the genomics community is really so big, they should just make their own file format (.gcvs or something) and make an Excel plugin that treats it as literal text cells.
• alphanumeric0 6y ago
  Research software dev here. This came as a huge shock to me when I started at my job. I work with very smart, dedicated people performing cancer research, why would they put up with this affecting their productivity? Humans really are adaptable creatures. After a few months of working there my boss handed me 3 or 4 Excel spreadsheets to compare to ensure a recent change I made hadn't affected our data (we don't have much in the way of automated tests either). As a software developer, this was a deeply troubling request. One option was to load them in to database tables so that I could perform SQL queries against the data (Postgres has COPY that works with CSVs), which isn't hard and probably the path most people should take, but I didn't want to write table definitions. I ended up using https://github.com/BurntSushi/xsv https://github.com/BurntSushi/xsv (I am not affiliated with the project in any way). It's a command-line tool written in Rust that performs queries/joins/manipulation/basic analysis against CSV/TSV files. While not as analytically powerful as Excel or Postgres, I was able to verify the data was good and pipe out results into another file without writing any custom code, and without opening a single file.
  • MaxBarraclough 6y ago
    Were the files expected to be identical? If so, diff would have done the job. Perhaps not directly relevant, but the lesser known GNU Recutils looks neat. Perhaps some day I'll find an opportunity to try it out. https://www.gnu.org/software/recutils/manual/recutils.html#Introduction https://www.gnu.org/software/recutils/manual/recutils.html#I...
  • teruakohatu 6y ago
    Have you checked out the tidyverse suite of packages on R? The tidyverse philosophy is don't make assumptions about data so you get detailed error messages when one row probably was not parsed correctly.
• deleted 6y ago
  [deleted]
• c3534l 6y ago
  I've always stood by the principle that a computer should never "correct" human input without asking and this is a great example of that. There should be a prompt asking if you want to convert the data, and there should always remain a way to undo "corrections," but it should never happen without the user knowing about it or wanting it to happen.
• njarboe 6y ago
  "There’s no easy fix, either. Excel doesn’t offer the option to turn off this auto-formatting, and the only way to avoid it is to change the data type for individual columns. Even then, a scientist might fix their own data, but as soon as someone else opens the same spreadsheet in Excel without thinking, errors will be introduced all over again." These are easy fixes. I have this problem using excel. Just format your data as text. If you do that and give the excel file to someone else the errors will not be introduced again. The quote above is at best misleading and I would say just wrong. The problem only happens when you import data in a non-excel format like comma delimited. In that case you have to click on a button when importing to excel to import it as "text" instead of automatic excel formatting. It would be great if you could set this as the default (maybe you can, but I don't know how), but it is something you get used to very quickly. Think of all of the financial people that use excel that would have this same problem. I'm sure there are some mess ups, but I don't think that industry has the same endemic problem with this excel file formatting issue. There are definitely problems with using excel, but I don't think this would have to be one of them. Edit: Of course it is not an easy fix to get everyone to use Excel correctly, but these types of bad data errors come from all over the place if the person using the data is not careful.
• danielecook 6y ago
  I'd love to see a better alternative to Excel geared more towards scientists. While it is true that R and python should be used for analysis, its often hard to escape a tool like Excel for data entry or browsing datasets. It would be nice if there was something out there that was faster and could handle larger datasets, and had features designed to help with data entry and browsing. I am envisioning something with double-entry data checking, enforcement of data types, and rules to check validity. Equally important would be the features it would lack - no tools to color or format text, no variable font sizes, no embedded images, etc.
• nayuki 6y ago
  https://www.youtube.com/watch?v=yb2zkxHDfUE&t=696 https://www.youtube.com/watch?v=yb2zkxHDfUE&t=696 "When Spreadsheets Attack!" by Stand-up Maths
• iaw 6y ago
  My favorite excel feature is when it randomly flips a 0 to an 8 or vice-versa in really long integers. Took a long time to figure out what was going on with that one.
• randompwd 6y ago
  So if someone stopped at the byline, they would have thought it was 'excel dumb' issue: > Sometimes it’s easier to rewrite genetics than update Excel rather than y'know, scientists cant properly use a tool they're using. bad journalist. edit: it even seems some of the commenters here stopped at the byline. quelle surprise
• Florin_Andrei 6y ago
  > Bruford’s theory is that it’s simply not worth the trouble to change Not worth it for the Microsoft power brokers. Very much worth it for a lot of people. And so the world is sometimes shaped by the narrow money interests of a very small group.
• dekhn 6y ago
  In grad school I studied a gene which at the time was called Oct1 ("octamer binding protein 1"). My main problem was literature searches, which often found "OCT-1" (organic cation transporter-1). Genomic naming is a total mess, I found it easier to just mentally compute the md5sum of a name, then memorize the first few digits (only need about 8-10 hex digits).
  • DMLoeffe 6y ago
    > just mentally compute the md5sum of a name What are you taking about lol
    • aqme28 6y ago
      > mentally compute I think he means "remember"
      • dekhn 6y ago
        md5 is easy- you only have to remember a few hundred constants (they can also be computed on the fly) and do basic bit operations on a small output (16 bytes).
• foresto 6y ago
  Somewhat tangential: If you use software in American English but dislike American date and time formats, you might see if your OS respects the en_DK locale setting. It works on most recent linux systems I've tried. $ LC_TIME=en_US.UTF-8 date '+%x %X' 08/06/2020 01:00:00 PM $ LC_TIME=en_DK.UTF-8 date '+%x %X' 2020-08-06 13:00:00
• Eyas 6y ago
  I'm surprised no one here mentioned backwards compatibility as the likely ultimate reason Microsoft hasn't fixed this by default yet. As others have mentioned, the "fix" of turning off auto-formatting is already available. But many here are wondering my Microsoft hasn't fixed the default. I assume it's for the same reason Microsoft Excel purposely claims the date 1900-02-29 is valid, to be compatible with Lotus-1-2-3. I assume many legacy spreadsheets would break unpredictably if interpreting values as dates by default changed.
• hermitcrab 6y ago
  Excel is an amazing tool. But it also has some significant shortcomings: * A well known tendency to mangle date and gene data under the guide of being 'helpful'. * Easy to making mistakes when cutting and pasting cells. * Difficult to see what is going on in a spreadsheet. * Poor handling of CSV files. Some of these shortcoming are inherent to spreadsheets. Others are specific to Excel, but hard to overcome due to the weight of backward compatibility. I have written a product for transforming and analysing tabular data (https://www.easydatatransform.com https://www.easydatatransform.com) that tries to overcome these issues: * Doesn't change your input data file. * Doesn't re-interpret your data, unless you ask it to. * See changes as a visual data flow. * Operations happen on a whole table or column. * Good handling of CSV files. Also it doesn't try to do everything Excel does. It is a fairly new tool. Would appreciate some feedback.
  • zuno 6y ago
    Thanks. I just checked the video. Looks neat! Excited to try it out.
  • tucaz 6y ago
    Website looks great. Will try shortly and I'm more than willing to give you my money if it delivers. We need a product like this. Edit: great call out to 7 days of non consecutive use
  • gverrilla 6y ago
    I'm a business guy, so I don't know much about it, but looks very useful and easy! I have been off the game for 5 years now, but I used to run a magento ecommerce, and I think your software might have helped managing products listings and stuff like that at the time. You might wanna take a look at this costumer segment, particularly small and medium businesses. They probably have a process for this already, but your software might be a good replacement, even though they won't be actively looking for it because they already have something that works. Good luck!
    • hermitcrab 6y ago
      Thanks. I will see what I can find out.
• ezekiel68 6y ago
  I have heard of life imitating art -- but I must say I find it a little eyeroll-worthy to contemplate that otherwise bright grad students and other researchers were unable to push through the struggle to understand and adapt to the quirks of MS Excel that many of the rest of us (in other fields) have needed to. I would have sooner expected Microsof to have added a "gene name" cell formatter option than for this to have occurred.
• vmchale 6y ago
  Load-bearing bug :p
• stjohnswarts 6y ago
  Who actually uses a tool for critical work and doesn't understand it enough to get around such annoyances? I have a bunch of gripes about various languages I use but I know that they are standard issue and nothing I can do about it so I work around them.
• xchip 6y ago
  Why are they usin excel on the first place?
• husamia 6y ago
  This was right decision to make. Gene names aren’t as important as being able to handle them properly. Names should be easily handled by software.
• xenonite 6y ago
  I suppose it is not even enough to check and change "JANUARY1": in Spanish it is "ENNERO1". So one needs to check the word in every language for which Excel exists. And in every language that Excel supports in the future.