11 ms·

One in five genetics papers contains errors thanks to Excel (2016)

407 points by redbell 2y ago

mbreese 2y ago
Even when this paper was published, errors due to Excel mangled gene names were well known for at least a decade. Somewhat recently, some of the more error prone genes were renamed to accommodate Excel. (Ex: SEPT7 -> SEPTIN7)
b112 2y ago
It's not really Excel, is it? But users not reviewing what's happening, not paying attention.
- marcosdumay 2y ago
  Yes, the problem is using Excel, not Excel by itself.
- happytoexplain 2y ago
  This attitude is at the root of why software is so much worse than it could be. "It's the users' fault" as a real root cause is borderline non-existent, because it can always be obviated in the software, even though the kind of talent that can do that for complex systems is exceedingly rare.
  - TeMPOraL 2y ago
    I disagree. The dominating attitude today is that users are idiots and too dumb to use anything they can't master in 30 seconds from first exposure, which has a nice side effect of cutting out a lot of functionality from the scope. The result is, for example, 20 startups going after any given idea, each spending years refining a slightly different take on a useless set of simple features. (Actually, many of such products can be made better by replacing them with an Excel sheet, which is a big part of the reason why people who actually need to get shit done end up using Excel.)
    - Karliss 2y ago
      I would say that the problem with excel is that it makes two contradicting assumptions which is a red flag for bad design. "it is user's fault for not knowing how to use the tool" and "user is lazy idiot who won't setup the cell types themselves". Its a little bit of convince of not having to set cells as numbers in a program that is use mainly for crunching numbers, but having basic auto detection text/numbers would have covered 90% of use, the remaining 10% is unique for each user and trying to guess that is as likely be helpful as making things worse.
  - nitwit005 2y ago
    This is users using software in a way the designers never expected, decades after it was designed. Those users then chose to ignore warnings about using it. Newer programs like Google Sheets have better default behaviors. They have free access to it.
golergka 2y ago
I studied bioinformatics in university back in 2005, and the first subject we were trained on was not Python or Java, but using Excel. Our teachers explicitly told us about excel's conversions to dates and other pitfalls. I find it mind-boggling that some researchers still made these mistakes 10 years later. Excel is a wonderful tool. But you need to learn your tools and find out about possible footguns.
- rectang 2y ago
  Haranguing your users not to make mistakes is utterly worthless. The only way to move the needle is to design systems that are easier to use correctly.
  - nitwit005 2y ago
    This isn't Microsoft lecturing them. It's entirely valid for the school to tell students to avoid easily avoidable pitfalls.
    - rectang 2y ago
      Of course they can lecture them — it just isn't going to work. The error rates will barely budge. There is one effect: it allows smug gloating about how stupid, lazy, and irresponsible these users are. Blaming the user is the last refuge of the incompetent.
      - TeMPOraL 2y ago
        > Blaming the user is the last refuge of the incompetent. Why then is it the dominating mindset in software design today, and advertised as being the opposite to the mindset that gives you Excel?
  - prepend 2y ago
    Excel is not a genetics tool. It has millions if use cases, many more important than genetics. Excel didn’t care about this. It’s like people complaining because sugar gets misused. Or that murderers stab people with knives. The solutions isn’t to “fix” knives.
rectang 2y ago
Only one in five? Evidence of egregious overengineering by the Excel team.
HenryBemis 2y ago
Let me reword this. "One in five genetics papers contains errors because the authors were careless when using Excel". I dislike Excel for what it does (EUCs, nearly impossible to track changes, etc.) But on the other hand it is an amazing tool.
- monocasa 2y ago
  > One in five genetics papers contains errors because the authors were careless when using Excel Or Excel is a remarkably easy tool to mishandle because of generally unexpected transformations it makes 'for you' automatically in an easy to miss way.
  - prepend 2y ago
    You think Excel should make special accommodations because genetics authors misuse it? Excel has been acting this way since before bioinformatics existed. Authors need to use their tools properly.
    - chucksmash 2y ago
      Excel (and GSheets) are tools where I think a stripped down "trust my input, I know what I'm doing" mode that doesn't try to intuit user intent would be useful. As I type this comment and my phone miscorrects "intuit" to "Intuit," I think also Google keyboard could benefit from such a mode that only handles spelling mistakes but doesn't replace uncommon words with common brands, etc.
      - tengwar2 2y ago
        But if you actually do know what you are doing, you know to set the cell type appropriately.
        dehugger 2y ago
        Yes, of course. Every cell as type "Text" so it doesnt screw up everything I paste... The real problem is the behavior of the default "General" type, which actually means "guess at every value and ham up all my data." I frequently have to paste in strings which consist of 0 prefixed number ids. I know very well to make sure the column is text before pasting, but other users don't always remember and frequently get their data messed up by the behavior of "General", which assumes that what you wanted was an integer and thus "helpfully" strips all the prefixed 0s.
deleted 2y ago
[deleted]
erehweb 2y ago
Original paper https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...
Prickle 2y ago
Previous popular thread, Aug 2020: https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385 > Scientists rename human genes to stop MS Excel from misreading them as dates (theverge.com) Related details: 2023: https://www.pcmag.com/news/microsoft-finally-fixes-excel-glitch-that-caused-major-headaches-for-scientists https://www.pcmag.com/news/microsoft-finally-fixes-excel-gli... > Years after introducing Excel's automatic conversion features, Microsoft rolls out an update to prevent it from changing gene symbols to dates. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9325790/ https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9325790/ > Gene Updater: a web tool that autocorrects and updates for Excel misidentified gene names
- deleted 2y ago
  [deleted]
magicalhippo 2y ago
I use Excel a fair bit at work, mostly for looking at data, but also to fairly easily generate SQL statements based on such data, often after some formulas have been applied. Though I think what I'd really want is some tool which has the same grid-like visualization, filtering and direct entering, but was code-based under the hood and without magic conversions. So, take Excel, and when you enter a formula in a cell, it actually writes a line of code for you, which you can inspect and edit. Including adding your own functions and such. When importing delimited text data, you'd have to specify what the data is in each column. It should still save the original text data so you can change your mind, but yeah, no automagic stuff.
- lukan 2y ago
  Yes, something like this is on my mind since quite some years as well. It would be limited to programmers, though.
- scrlk 2y ago
  The Data Wrangler extension for VS Code might be of interest. As you apply operations to your dataset, it generates Pandas code. https://code.visualstudio.com/docs/datascience/data-wrangler https://code.visualstudio.com/docs/datascience/data-wrangler https://www.youtube.com/watch?v=5tWJVLF6PuA https://www.youtube.com/watch?v=5tWJVLF6PuA
redbell 2y ago
Here's another interesting article on the same topic (2016) entitled "Gene name errors are widespread in the scientific literature" : https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13... From the article: "The problem of Excel software inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively."
hilbert42 2y ago
Don't we ever learn. We've known since VisiCalc in the '80s not to trust spreadsheets for important data without crosschecking the results. The question is why do we still use substandard tools for processing important data like this.
- lukan 2y ago
  "The question is why do we still use substandard tools for processing important data like this." Because there is no better alternative (yet)? A better alternative needs to be really better, to justify the effort of people relearning how to do things in this better tool then.
  - jasinjames 2y ago
    I think another factor is that the spreadsheet model has basically zero barrier to entry from a users perspective. You can have elementary school students punch data from a classroom experiment into an excel table, which is great! Any replacement system which, for example, enforced a strong separation between operations, input reference data and output result data would require users to learn the model before attempting to use the software. This is a pretty big ask, especially since lots of small-scale users wouldn't see an immediate benefit. I think of it like the tradeoff between dynamic and static typing when programming- it's the same "upfront mental overhead versus long term maintainability" question IMO.
  - eviks 2y ago
    There isn't much relearning needed since errors like the one described in the article illustrate that people don't know much about this tool either And Sheets isn't "really better", yet gained a noticeable share
freehorse 2y ago
At work we have had problems with excel changing values of numerical entries due to different locale formatting multiple times in the past, and many times causing hours of lost time till the culprit (excel) was found. Other types of values that are completely inconvenient for excel include social security numbers and phone numbers; which one would assume are more general purpose and thus closer to the standard use cases of excel than genes. Still excel is messing these things up. Opening a csv file is also a non-trivial overly complicated endeavour for excel in year 2024, which should be done with a double click (somehow other spreadsheet programs manage to handle csv files much more easily). The worst is that excel is usually used by non-technical people, who misunderstand these idiosyncrasies and time is wasted over and over.
- yial 2y ago
  My work frustration with excel is not so much excel itself, but that people will destroy / delete / not understand formulas (or think they’re improving them…) and then the end result of their edits is garbage. However this is my fault usually for not using the protect / lock features well enough. I do know that notes explanations get overlooked. I’ve mitigated this slightly by almost always having in our shared a blank start, that’s just a backup to start from, and then the actual one to be used.
theodpHN 2y ago
So, the question is: Would there be fewer or more errors if something other than Excel was used? Has much rigorous research been done on this?
- eviks 2y ago
  Unfortunately researchers can't fork the universe with a different set of defaults for this rigorous study you appear
- Djdjur7373bb 2y ago
  One should probably also ask how much research would not have gotten done or other issues that would crop up if less tech-savvy researchers didn't have a beginner friendly tool like Excel available.
Gimpei 2y ago
Didn’t the same thing happen in economics because of a bug in Stata in the 90s?
bikenaga 2y ago
An annoyance with spreadsheets that deterred me from ever using them in teaching is that they've perpetuated a arithmetic order-of-precedence bug. ("Bug" in the sense that it contradicts long-standing mathematical convention.) If you type -3^2 in a cell and press ENTER, the spreadsheet tell you it's "9". It should be "-9"; in math, exponentiation has precedence over unary minus, so you square 3, then negate the result. For instance, if you tell students to graph "y = -x^2", they should draw a parabola opening downward. I don't have a recent copy of Excel to check this in, but this was the case in the '97 version. I just tried it in the current LibreOffice calc, and it returns "9". My guess is that one of the early spreadsheets messed up the order of precedence, and everybody after copied it for compatibility. On the other hand, I just tried maxima and python and they both give "-9". I wonder if this particular problem afflicts people who copy formulas from (say) math books into spreadsheets.
- mobilio 2y ago
  But it's 9! Check it: https://www.mathplanet.com/education/pre-algebra/explore-and-understand-integers/multiplying-and-dividing-with-integers#:~:text=You%20also%20have%20to%20pay,the%20product%20is%20always%20positive https://www.mathplanet.com/education/pre-algebra/explore-and.... "You also have to pay attention to the signs when you multiply and divide. There are two simple rules to remember: When you multiply a negative number by a positive number then the product is always negative. When you multiply two negative numbers or two positive numbers then the product is always positive." So basically you have -3x-3 and result is 9.
  - n_plus_1_acc 2y ago
    Correct, but irrelevant to the question. The usual rules of math require it to be parsed like -(3²), so there are only positive numbers being multiplied.
  - shagie 2y ago
    https://www.wolframalpha.com/input?i=-3%5E2 https://www.wolframalpha.com/input?i=-3%5E2 https://en.wikipedia.org/wiki/Order_of_operations https://en.wikipedia.org/wiki/Order_of_operations Parentheses, Exponentiation, Multiplication, Division, Addition, Subtraction -3^2 would then be correctly parsed as -(3^2) which is -9. Parsing it as (-3)^2 would require the addition of parentheses. This gets to the special case of the unary minus sign... which the Wikipedia article specifically calls out. Special cases Unary minus sign There are differing conventions concerning the unary operation '−' (usually pronounced "minus"). In written or printed mathematics, the expression −3² is interpreted to mean −(3²) = −9. In some applications and programming languages, notably Microsoft Excel, PlanMaker (and other spreadsheet applications) and the programming language bc, unary operations have a higher priority than binary operations, that is, the unary minus has higher precedence than exponentiation, so in those languages −3² will be interpreted as (−3)² = 9. This does not apply to the binary minus operation '−'; for example in Microsoft Excel while the formulas =-2^2, =-(2)^2 and =0+-2^2 return 4, the formulas =0-2^2 and =-(2^2) return −4. (edit) Digging into this a little bit more... https://www.gnu.org/software/bc/manual/html_mono/bc.html#TOC13 https://www.gnu.org/software/bc/manual/html_mono/bc.html#TOC... The expression precedence is as follows: (lowest to highest) || operator, left associative && operator, left associative ! operator, nonassociative Relational operators, left associative Assignment operator, right associative + and - operators, left associative *, / and % operators, left associative ^ operator, right associative unary - operator, nonassociative ++ and -- operators, nonassociative This precedence was chosen so that POSIX compliant bc programs will run correctly. This will cause the use of the relational and logical operators to have some unusual behavior when used with assignment expressions. Consider the expression: ... This brings us to the POSIX specification for bc https://pubs.opengroup.org/onlinepubs/9699919799.2018edition/utilities/bc.html https://pubs.opengroup.org/onlinepubs/9699919799.2018edition... This also shows the unary - having higher precedence than ^. https://github.com/gavinhoward/bc/blob/master/manuals/development.md https://github.com/gavinhoward/bc/blob/master/manuals/develo... This document is meant for the day when I (Gavin D. Howard) get hit by a bus. In other words, it's meant to make the bus factor a non-issue. This document is supposed to contain all of the knowledge necessary to develop bc and dc. In addition, this document is meant to add to the oral tradition of software engineering, as described by Bryan Cantrill. ... now, it would be interesting if gavinhoward could clarify some of the design thoughts there (and I absolutely love the oral tradition talk).
jasinjames 2y ago
I had a similar issue just two weeks ago at $DAYJOB. I was scraping a log file and I had written an awk script to convert the HH:MM:SS.uuuuuu timestamps to nanoseconds since epoch for ingestion by another tool. Little did I know that awk uses floating point for numbers internally, and so my conversion function was incorrectly rounding the result, which messed up my later analysis in arcane ways. To add insult to injury, I had the same problem again when paging through the data in Visidata, but there was an easy workaround I don't exactly remember.
smartmic 2y ago
The problem with Excel is that it tries to do almost everything in one software tool: front-end (editing, presentation), analysis, semantic modeling, data storage/database. Almost all reasonable engineers see that there is something wrong with such an approach. But almost all everyday computer users think that this is the way computing has to be. Sometimes I wonder why even I voluntarily open it for certain tasks - anyway, despite all the criticism, Excel has reached the Lindy[1] threshold for me and is here to stay. [1] https://en.wikipedia.org/wiki/Lindy_effect https://en.wikipedia.org/wiki/Lindy_effect
- ravetcofx 2y ago
  Except that it's proprietary and will eventually be un-maintained and stop working. But spreadsheets, in general, fall under the Lindy effect and open source software will continue it for centuries to come.
  - Closi 2y ago
    Although excel spreadsheets have had more longevity than lots of other formats…
  - Dalewyn 2y ago
    You might have had a point in the days of .xls, but Excel by default uses .xlsx now which is just XML packaged inside a .zip archive if I recall. And yes, Excel still fully supports .xls too. I fear whatever format LibreOffice uses will die first, case in point I don't even remember what it's called even though I should as a computer nerd.
    - wizzwizz4 2y ago
      MS-OOXML is barely an open format. Have you tried implementing it? ECMA-376 part 1 is over 5000 pages, and there are four parts to it! (Part 1 contains an extra bit about SpreadsheetML, but by that point we were two zips deep. I turned back ere I got lost.) The OpenDocument formats, meanwhile, are older, simpler and better than their MS-OOXML equivalents. (The ODF spec is 1041 pages altogether – 215 pages of that are the spreadsheet formula language.) LibreOffice's implementation is a little janky, sure, but I can edit OpenDocument files by hand. Try doing that to a MS-OOXML file. (Good luck.)
darkhorn 2y ago
This is why Statisticians do not use Excel for scientific work. They use SPSS, R, Minitab, MATLAB, SAS, etc.
- antegamisou 2y ago
  Yeah I was about to say that the news has to be that Excel has extensive use in biomedical research.
Eddy_Viscosity2 2y ago
But why oh why can't you disable automatic date reading? Put something in settings with a checkbox that I, the user, can tell excel to F-off with that nonsense. Is there anybody who can argue the 'for' case for having this on all the time without recourse?
- simonh 2y ago
  Aside from setting all your cells to text, they have added checkboxes for this now. https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac https://insider.microsoft365.com/en-us/blog/control-data-con...
  - Eddy_Viscosity2 2y ago
    I just tried this out. I'm using office 2021 and when I first opened excel, this setting was there just like in the link and it seemed to work when I entered a few strings in. 'Fantastic!' I thought. Then I closed it and thought of a few other date-like strings to try and this time the option had disappeared! Every date-looking string was instantly turned into date! I tried a few other times and this setting is gone. WTF is that about?
    - simonh 2y ago
      Have you opened an older file? Maybe it only works with files created with the feature. I’ve no idea why that would be the case, but it’s all I can think of.
      - Eddy_Viscosity2 2y ago
        Both times (and a few after that), it was accessing excel settings before opening any files or starting a new one. I've tried after opening files, same thing. The setting is just not there like it was the first time.
- XlA5vEKsMISoIln 2y ago
  Introducing checkboxes somewhere in the bowels of settings adds more ways for things to go wrong. What is the default? Do the defaults differ across versions? How do you keep it consistent across computers and installations? What if you actually need the function ad hoc? This reminds me of CSV export. I haven't used Windows for a decade but I remember that if you wanted to change how decimal numbers were exported you had to change the locale and reboot the computer. To change a setting in Excel. That is insane. Sprinkling checkbox patches isn't too far from this.
bluedino 2y ago
Missing checkbox. "Assume Everything Is A String" We used to hit all kinds of Excel weirdness with inventory etc. It was our fault that our part numbers could look like this: 00010190-95.020
- alexlll862 2y ago
  That feature already exists. It's technically not a "checkbox", but you can set a cell (or all of them) as string type in like 3 clicks.
  - Arrath 2y ago
    Still, an easy footgun to stumble into if you weren't mindful when setting up your spreadsheet
  - epistasis 2y ago
    The problem was that you could not import the data without it being destroyed. By the time you could see it in Excel, the conversion had already been completed, without a way to get text.
databoy24 2y ago
Excel will convert many CUSIPs (strings) into numbers (scientific notation). For a relatively comprehensive list, see below. https://www.empirasign.com/cusip-excel-rosetta/ https://www.empirasign.com/cusip-excel-rosetta/ Barring some types of corporate actions, CUSIPs numbers cannot change, and I doubt the ABA is aware of this issue.
bvan 2y ago
Sounds like user-error, not a software error. Learn to use the software and its idiosyncrasies. RTFM.
- AllegedAlec 2y ago
  God I hate programmers so much. "Hurdurdur it's not my fault. The end users are wrong. We shouldn't have to spend time creating an interface and UX that actually works as expected" Absolute fucking retards. We make tools for these people. If these tools do not work as end users would expect to that is our mistake. Stop coping about 'training end users'.
  - Biganon 2y ago
    This. There is absolutely no excuse for Excel's behavior here. It's been DECADES and they still haven't added a goddamn dialog that says, upon importing data, "OH, it looks like your data contains stuff that looks like dates, are they dates, or are they NOT DATES? Thanks, have a nice day"
    - naught0 2y ago
      This does indeed exist in the latest version I have running. When opening a CSV all automatic conversion can be disabled
  - ozim 2y ago
    That's not programmers, that's business analysts, product owners, quality assurance, programmers only code what is asked for. Having score set straight - not everything can be made "just do the UX that actually works" because there is more users and more "what actually works" than you can implement. Not everything can be "just simple", excel for instance is powerful beast but it is powerful because it is complex and one can do really complex stuff with it. I can make simple spreadsheet software but no one will be using it because it will not allow to do really complex stuff.
a-dub 2y ago
kinda like automatic fallback or other automatic help/fix features. they sound like something that would be helpful but in practice they just end up being a massive violation of the principle of least surprise.
codr7 2y ago
At least we got rid of Access? I've seen things created in MS Access that can't be unseen.
__mharrison__ 2y ago
One wonders if these scientists took a week to learn sufficient Python and pandas, how much better the world would be? (Certainly possible, I teach "I don't want to be a programmer"-types all the time. Taught a class this week in fact.)
- ClassyJacket 2y ago
  I work with this type of scientists, supporting them in an IT capacity. Many of them do try to learn Python, I've been quite impressed by it, but realistically Excel isn't going away any time soon regardless. Sometimes it's the right tool for the job.
aaron695 2y ago
[dead]
jtrueb 2y ago
Quit using Excel in research!
deleted 2y ago
[deleted]
jimnotgym 2y ago
Fixing the headline... One in five genetics papers have errors caused by mistakes in the use of Excel As if longhand calculations never have errors?
- remus 2y ago
  The difference here is that the excel errors are systematic so should be more easily avoidable, whereas long hand calcs are more varied and harder to correct.
  - jimnotgym 2y ago
    I use Excel a lot. These things don't trip me up in my field. Could it be that this field is a rather niche use case, and therefore the users should learn to watch out for them?
stellalo 2y ago
> The researchers […] say the issue can be fixed by formatting Excel columns as text and remaining vigilant—or switching to Google Sheets, where gene names are stored exactly as they're entered. I think the safest fix is to avoid spreadsheets altogether, as long as scientific research is concerned.
v1ne 2y ago
We have a saying in German: A bad worker always blames their tools. Excel has many quirks, but I'm still very grateful that it exists, for quickly putting together some numbers and still being able to change the inputs to my formulas.
- AStonesThrow 2y ago
  That's interesting. In English, the idiom is: "It's a poor artist who blames his tools."
  - thebytefairy 2y ago
    As a native English speaker, I've actually only heard 'a bad workman blames his tools', not the artist form. Now that I google it, did you mean craftsman? I don't see any references to artist.
- hennell 2y ago
  A poor worker blames their tools, but no-one can write the Bible on a stamp with a carrot...
- augustk 2y ago
  I think what you mean here is that you are grateful that spreadsheet applications exist. https://simple.wikipedia.org/wiki/Spreadsheet https://simple.wikipedia.org/wiki/Spreadsheet
- asjir 2y ago
  In Polish we have: a bad ballerina is hindered by the hem of her skirt
- jyunwai 2y ago
  But at the same time, there is a saying in user experience design: don't blame the user. At least at the time of the article, there was no way to disable the auto-conversion of certain strings (like "SEPT2") into dates. A setting to disable this would have stopped many errors amplified by researchers working late at night or rushing to meet a deadline. It's true that there has to be some point where the users of the tool need to put in the effort to learn how to best use it. But effort poured in from the other end by the developers, too, can go a long way to prevent common errors and save users time.
zelphirkalt 2y ago
Know your tools. When I see such headlines, I feel sad about the state of this world and obstacles to progress, angry about the stupidity of designing all the tools for the most lazy of users, and at the same time I also laugh about the failures of proprietary software and its users, who could easily have invested a little bit of time to learn alternatives, but apparently chose not to. Yes the tooling they use might be terrible. It is your responsibility to either deal with the terrible tooling yourself, learn better tooling, or get a capable computer person in the room, who can navigate the tooling landscape and get you the results. And of course, that is not even addressing checking your result yet. This is a sad state of the research landscape, often financed by public money, and then throwing money at MS for using a proprietary tool and messing up.
- dmvdoug 2y ago
  Yes, tools are not made for users but for the tool-builders. Everyone else must adapt!
  - itchyjunk 2y ago
    A single tool must work for ever use case or should the tool be picked based on the use case? Tool X doesn't work for use case Y. Regardless of where you shift the blame, tool X doesn't work for use case Y holds.
  - CivBase 2y ago
    Excel is made for spreadsheets, not databases. Microsoft already has a database application in Access.
  - brookst 2y ago
    I think you’re reframing “when all you have is a hammer, every problem is a nail” into “when all you have is a screw and a hammer, it’s outrageous the hammer wasn’t designed to drive screws”.
  - mrgoldenbrown 2y ago
    Tools are designed for a particular use case/userbase. Excel was not designed for gene scientists. It wouldn't make sense for me, a home owner who uses a jigsaw to cut molding, to complain to a chainsaw maker that their chainsaw isn't right for my job.
nuc1e0n 2y ago
A HN thread similar to this one about how using Microsoft Excel corrupts data occurs about once a month. Nothing ever comes of it. Those emotionally invested in using Excel actively ignore criticism of it, or themselves for that matter.
- jsmith99 2y ago
  Actually Microsoft have recently added a warning dialog about certain automatic csv conversions. Perhaps they finally read the thread. https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac https://insider.microsoft365.com/en-us/blog/control-data-con...
  - nuc1e0n 2y ago
    Yes I'm aware. It would be good to have a group policy or some such registry setting to disable them all by default for every computer on the network.
wodenokoto 2y ago
What do you guys recommend for viewing and scrolling around tabular data? Most of my excel usage is literally just double clicking to have a look at a file.
gverrilla 2y ago
The problem with most software for complex tasks, including Excel and Python, is the hidden and/or non-intuitive mechanics. That's why a AI companion should accompany them for non-expert users (99%), which would be able to check what the user is doing for intention-result errors and warn.
huijzer 2y ago
No, it is because incentives are misaligned and people care about getting cited. It doesn’t matter whether it’s correct or not.
lbriner 2y ago
Don't get me started on the automatic conversion of strings that it thinks are in scientific notation into numbers - which you can't switch off! We have large data exports from systems that include things like unique location code. You accidentally happen to notice that a block of these look weird and it isn't just the display of them that has changed, the contents of the cell were changed by Excel automatically, without asking, and you cannot disable it. Absolute BS after all these years. I hate that they won't fix these niggling issues that keep tripping people up over the years and just make excuses. Microsoft's usual response is: "We only work on things that affect a large number of customers". Yeah Microsoft, if you keep closing these bug reports, then each time someone reports it, you can just say that it only affects one person and close it again. Or...you could show how amazing your company is by doing what most of us have to do: Fix it, add more debugging for the next time it happens if you can't recreate it, or have a properly tracked reason to say, "only a very few people have asked for this but changing it might break these other areas/bacwards compatability" or something.
- tssva 2y ago
  Is there a reason you don't specify the data type when importing or if it is a frequently used data file format automate the import using VBA specifying the correct data type?
  - mark-r 2y ago
    If you open a CSV file without going through a specific import process, you don't even get the option to specify a data type. And once it's open it's too late to fix it, the original data is already gone.
    - tssva 2y ago
      The specific import process isn't some sort of esoteric process. It is the data import wizard. Also as I said if you are regularly importing data from a file with the same format writing some VBA to do so is pretty quick and simple task. Also your data isn't gone. It is still in the CSV file you imported it from. Re-import it.
dugmartin 2y ago
US ZIP codes are what I always have issue with during imports. They start with 0 in New England and the zero always gets removed.
moi2388 2y ago
The problem is scientists insisting on using tools like excel and Python instead of decent type safe language to do their calculations in
mixedbit 2y ago
Does Excel have some reasonable way of unit testing of formulas?
- nhatcher 2y ago
  No, as far as I know. And I have been thinking of ways to that fir the better part of the last decade. I think bringing modern programming practices into Excel might make huge differences. But I don't know how to do that in an Excel user friendly way
temptemptemp111 2y ago
[dead]
amai 2y ago
Thank got we don't have a monopoly in office software and can easily switch to a competitor.
ThinkBeat 2y ago
This seems to be a problem quite smilar to dynamic or static typing. The users expect that Excel will figure out what to do with the input correctly. But you can manually tell Excel what to do with data in a column.
benrutter 2y ago
I know that the "anything" goes nature of excel is part of what makes it such a universally reached for tool, but every time I see sonething like this, I think that there would be a clear use case for a more restrictive spreadsheet. 9/10 spreadsheets are tables, but because they're spreadsheets they inherit the behaviour of "no conistent behaviour in columns, everything is independent and different".
- zamadatix 2y ago
  You can set the format of the column when making a table. Much more than that and you've ended up at Access. The problem here with Excel is it's wonky default until you've made that explicit choice.
BurningFrog 2y ago
This is 100% on the scientists who continue to use a tool that doesn't work for their use case!
- ttoinou 2y ago
  Maybe they are locked into the tool because of legacy reasons, and they don’t have a developer around in their team responsible for upgrading their tool / software workflow. This must be a non-trivial problem for a lot of workers all around the world
oglop 2y ago
lol. What a solution. Maybe learn how to use a proper DB. Research science is like 14 years behind the times. I was amazed at the SciPy24 conference how almost no data scientists use DBs. Wild. Pandas is your friend here and can at least help you with json stuff. Using excel though, yeesh. Yeah the problem isn’t excel, it’s bad training on data handling from schools.
me_vinayakakv 2y ago
Reminds me of YAML's handling of booleans (`no` -> false) and git commit SHAs (treated as numbers in scientific notation)
robblbobbl 2y ago
lol
fifilura 2y ago
I always felt that there is a hierarchy for working with numbers that goes something like this Ease of use: 1. Excel 2. SQL 3. Functional programming (e.g. Scala, Python to some measure e.g. Pandas) 4. Imperative programming (C/C++/Java) But then there another hierarchy that (roughly) goes in the other direction, which is about quality, repeatability, tooling. If you are at 1 or 2, you responsibility will not be about writing tests and verifying your code using traditional engineering methods. However! You are responsible for cross checking your results based on the input. This may be a manual process. But actually looking at the numbers from several different angles can give higher quality than writing contrived testcases (in 3 or 4).
- anthk 2y ago
  2 s,SQL,j https://www.jsoftware.com/indexno.html https://www.jsoftware.com/indexno.html Also: https://www.jsoftware.com/help/dictionary/intro.htm https://www.jsoftware.com/help/dictionary/intro.htm EDIT: The help section has 6 books. If you want, you can do self-teach yourself advanced math stuff with very few lines. I suggest to install Gnuplot as a dependency, for plots.
  - fifilura 2y ago
    Very good! I believe you. Unfortunately I feel like it would be irresponsible to transition our stack to working with J because of available competence and relearning. I can see it being used in research though.
om8 2y ago
Younger me would've said that WYSIWYG editors were a mistake and that researchers should've used LaTeX. Now I think these errors are a small price to pay for convenience. One could waste a lifetime fighting small things like this and still lose. It's just the world we live in.

407 points by redbell 2y ago

Even when this paper was published, errors due to Excel mangled gene names were well known for at least a decade. Somewhat recently, some of the more error prone genes were renamed to accommodate Excel. (Ex: SEPT7 -> SEPTIN7)

b112 2y ago

It's not really Excel, is it? But users not reviewing what's happening, not paying attention.

marcosdumay 2y ago
Yes, the problem is using Excel, not Excel by itself.
happytoexplain 2y ago
This attitude is at the root of why software is so much worse than it could be. "It's the users' fault" as a real root cause is borderline non-existent, because it can always be obviated in the software, even though the kind of talent that can do that for complex systems is exceedingly rare.
- TeMPOraL 2y ago
  I disagree. The dominating attitude today is that users are idiots and too dumb to use anything they can't master in 30 seconds from first exposure, which has a nice side effect of cutting out a lot of functionality from the scope. The result is, for example, 20 startups going after any given idea, each spending years refining a slightly different take on a useless set of simple features. (Actually, many of such products can be made better by replacing them with an Excel sheet, which is a big part of the reason why people who actually need to get shit done end up using Excel.)
  - Karliss 2y ago
    I would say that the problem with excel is that it makes two contradicting assumptions which is a red flag for bad design. "it is user's fault for not knowing how to use the tool" and "user is lazy idiot who won't setup the cell types themselves". Its a little bit of convince of not having to set cells as numbers in a program that is use mainly for crunching numbers, but having basic auto detection text/numbers would have covered 90% of use, the remaining 10% is unique for each user and trying to guess that is as likely be helpful as making things worse.
- nitwit005 2y ago
  This is users using software in a way the designers never expected, decades after it was designed. Those users then chose to ignore warnings about using it. Newer programs like Google Sheets have better default behaviors. They have free access to it.

golergka 2y ago

I studied bioinformatics in university back in 2005, and the first subject we were trained on was not Python or Java, but using Excel. Our teachers explicitly told us about excel's conversions to dates and other pitfalls. I find it mind-boggling that some researchers still made these mistakes 10 years later. Excel is a wonderful tool. But you need to learn your tools and find out about possible footguns.

rectang 2y ago
Haranguing your users not to make mistakes is utterly worthless. The only way to move the needle is to design systems that are easier to use correctly.
- nitwit005 2y ago
  This isn't Microsoft lecturing them. It's entirely valid for the school to tell students to avoid easily avoidable pitfalls.
  - rectang 2y ago
    Of course they can lecture them — it just isn't going to work. The error rates will barely budge. There is one effect: it allows smug gloating about how stupid, lazy, and irresponsible these users are. Blaming the user is the last refuge of the incompetent.
    - TeMPOraL 2y ago
      > Blaming the user is the last refuge of the incompetent. Why then is it the dominating mindset in software design today, and advertised as being the opposite to the mindset that gives you Excel?
- prepend 2y ago
  Excel is not a genetics tool. It has millions if use cases, many more important than genetics. Excel didn’t care about this. It’s like people complaining because sugar gets misused. Or that murderers stab people with knives. The solutions isn’t to “fix” knives.

rectang 2y ago

Only one in five? Evidence of egregious overengineering by the Excel team.

HenryBemis 2y ago

Let me reword this. "One in five genetics papers contains errors because the authors were careless when using Excel". I dislike Excel for what it does (EUCs, nearly impossible to track changes, etc.) But on the other hand it is an amazing tool.

monocasa 2y ago
> One in five genetics papers contains errors because the authors were careless when using Excel Or Excel is a remarkably easy tool to mishandle because of generally unexpected transformations it makes 'for you' automatically in an easy to miss way.
- prepend 2y ago
  You think Excel should make special accommodations because genetics authors misuse it? Excel has been acting this way since before bioinformatics existed. Authors need to use their tools properly.
  - chucksmash 2y ago
    Excel (and GSheets) are tools where I think a stripped down "trust my input, I know what I'm doing" mode that doesn't try to intuit user intent would be useful. As I type this comment and my phone miscorrects "intuit" to "Intuit," I think also Google keyboard could benefit from such a mode that only handles spelling mistakes but doesn't replace uncommon words with common brands, etc.
    - tengwar2 2y ago
      But if you actually do know what you are doing, you know to set the cell type appropriately.
      - dehugger 2y ago
        Yes, of course. Every cell as type "Text" so it doesnt screw up everything I paste... The real problem is the behavior of the default "General" type, which actually means "guess at every value and ham up all my data." I frequently have to paste in strings which consist of 0 prefixed number ids. I know very well to make sure the column is text before pasting, but other users don't always remember and frequently get their data messed up by the behavior of "General", which assumes that what you wanted was an integer and thus "helpfully" strips all the prefixed 0s.

deleted 2y ago

[deleted]

erehweb 2y ago

Original paper https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13...

Prickle 2y ago

Previous popular thread, Aug 2020: https://news.ycombinator.com/item?id=24070385 https://news.ycombinator.com/item?id=24070385 > Scientists rename human genes to stop MS Excel from misreading them as dates (theverge.com) Related details: 2023: https://www.pcmag.com/news/microsoft-finally-fixes-excel-glitch-that-caused-major-headaches-for-scientists https://www.pcmag.com/news/microsoft-finally-fixes-excel-gli... > Years after introducing Excel's automatic conversion features, Microsoft rolls out an update to prevent it from changing gene symbols to dates. https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9325790/ https://www.ncbi.nlm.nih.gov/pmc/articles/PMC9325790/ > Gene Updater: a web tool that autocorrects and updates for Excel misidentified gene names

deleted 2y ago
[deleted]

magicalhippo 2y ago

I use Excel a fair bit at work, mostly for looking at data, but also to fairly easily generate SQL statements based on such data, often after some formulas have been applied. Though I think what I'd really want is some tool which has the same grid-like visualization, filtering and direct entering, but was code-based under the hood and without magic conversions. So, take Excel, and when you enter a formula in a cell, it actually writes a line of code for you, which you can inspect and edit. Including adding your own functions and such. When importing delimited text data, you'd have to specify what the data is in each column. It should still save the original text data so you can change your mind, but yeah, no automagic stuff.

lukan 2y ago
Yes, something like this is on my mind since quite some years as well. It would be limited to programmers, though.
scrlk 2y ago
The Data Wrangler extension for VS Code might be of interest. As you apply operations to your dataset, it generates Pandas code. https://code.visualstudio.com/docs/datascience/data-wrangler https://code.visualstudio.com/docs/datascience/data-wrangler https://www.youtube.com/watch?v=5tWJVLF6PuA https://www.youtube.com/watch?v=5tWJVLF6PuA

redbell 2y ago

Here's another interesting article on the same topic (2016) entitled "Gene name errors are widespread in the scientific literature" : https://genomebiology.biomedcentral.com/articles/10.1186/s13059-016-1044-7 https://genomebiology.biomedcentral.com/articles/10.1186/s13... From the article: "The problem of Excel software inadvertently converting gene symbols to dates and floating-point numbers was originally described in 2004. For example, gene symbols such as SEPT2 (Septin 2) and MARCH1 [Membrane-Associated Ring Finger (C3HC4) 1, E3 Ubiquitin Protein Ligase] are converted by default to ‘2-Sep’ and ‘1-Mar’, respectively."

hilbert42 2y ago

Don't we ever learn. We've known since VisiCalc in the '80s not to trust spreadsheets for important data without crosschecking the results. The question is why do we still use substandard tools for processing important data like this.

lukan 2y ago
"The question is why do we still use substandard tools for processing important data like this." Because there is no better alternative (yet)? A better alternative needs to be really better, to justify the effort of people relearning how to do things in this better tool then.
- jasinjames 2y ago
  I think another factor is that the spreadsheet model has basically zero barrier to entry from a users perspective. You can have elementary school students punch data from a classroom experiment into an excel table, which is great! Any replacement system which, for example, enforced a strong separation between operations, input reference data and output result data would require users to learn the model before attempting to use the software. This is a pretty big ask, especially since lots of small-scale users wouldn't see an immediate benefit. I think of it like the tradeoff between dynamic and static typing when programming- it's the same "upfront mental overhead versus long term maintainability" question IMO.
- eviks 2y ago
  There isn't much relearning needed since errors like the one described in the article illustrate that people don't know much about this tool either And Sheets isn't "really better", yet gained a noticeable share

freehorse 2y ago

At work we have had problems with excel changing values of numerical entries due to different locale formatting multiple times in the past, and many times causing hours of lost time till the culprit (excel) was found. Other types of values that are completely inconvenient for excel include social security numbers and phone numbers; which one would assume are more general purpose and thus closer to the standard use cases of excel than genes. Still excel is messing these things up. Opening a csv file is also a non-trivial overly complicated endeavour for excel in year 2024, which should be done with a double click (somehow other spreadsheet programs manage to handle csv files much more easily). The worst is that excel is usually used by non-technical people, who misunderstand these idiosyncrasies and time is wasted over and over.

yial 2y ago
My work frustration with excel is not so much excel itself, but that people will destroy / delete / not understand formulas (or think they’re improving them…) and then the end result of their edits is garbage. However this is my fault usually for not using the protect / lock features well enough. I do know that notes explanations get overlooked. I’ve mitigated this slightly by almost always having in our shared a blank start, that’s just a backup to start from, and then the actual one to be used.

theodpHN 2y ago

So, the question is: Would there be fewer or more errors if something other than Excel was used? Has much rigorous research been done on this?

eviks 2y ago
Unfortunately researchers can't fork the universe with a different set of defaults for this rigorous study you appear
Djdjur7373bb 2y ago
One should probably also ask how much research would not have gotten done or other issues that would crop up if less tech-savvy researchers didn't have a beginner friendly tool like Excel available.

Gimpei 2y ago

Didn’t the same thing happen in economics because of a bug in Stata in the 90s?

bikenaga 2y ago

An annoyance with spreadsheets that deterred me from ever using them in teaching is that they've perpetuated a arithmetic order-of-precedence bug. ("Bug" in the sense that it contradicts long-standing mathematical convention.) If you type -3^2 in a cell and press ENTER, the spreadsheet tell you it's "9". It should be "-9"; in math, exponentiation has precedence over unary minus, so you square 3, then negate the result. For instance, if you tell students to graph "y = -x^2", they should draw a parabola opening downward. I don't have a recent copy of Excel to check this in, but this was the case in the '97 version. I just tried it in the current LibreOffice calc, and it returns "9". My guess is that one of the early spreadsheets messed up the order of precedence, and everybody after copied it for compatibility. On the other hand, I just tried maxima and python and they both give "-9". I wonder if this particular problem afflicts people who copy formulas from (say) math books into spreadsheets.

mobilio 2y ago
But it's 9! Check it: https://www.mathplanet.com/education/pre-algebra/explore-and-understand-integers/multiplying-and-dividing-with-integers#:~:text=You%20also%20have%20to%20pay,the%20product%20is%20always%20positive https://www.mathplanet.com/education/pre-algebra/explore-and.... "You also have to pay attention to the signs when you multiply and divide. There are two simple rules to remember: When you multiply a negative number by a positive number then the product is always negative. When you multiply two negative numbers or two positive numbers then the product is always positive." So basically you have -3x-3 and result is 9.
- n_plus_1_acc 2y ago
  Correct, but irrelevant to the question. The usual rules of math require it to be parsed like -(3²), so there are only positive numbers being multiplied.
- shagie 2y ago
  https://www.wolframalpha.com/input?i=-3%5E2 https://www.wolframalpha.com/input?i=-3%5E2 https://en.wikipedia.org/wiki/Order_of_operations https://en.wikipedia.org/wiki/Order_of_operations Parentheses, Exponentiation, Multiplication, Division, Addition, Subtraction -3^2 would then be correctly parsed as -(3^2) which is -9. Parsing it as (-3)^2 would require the addition of parentheses. This gets to the special case of the unary minus sign... which the Wikipedia article specifically calls out. Special cases Unary minus sign There are differing conventions concerning the unary operation '−' (usually pronounced "minus"). In written or printed mathematics, the expression −3² is interpreted to mean −(3²) = −9. In some applications and programming languages, notably Microsoft Excel, PlanMaker (and other spreadsheet applications) and the programming language bc, unary operations have a higher priority than binary operations, that is, the unary minus has higher precedence than exponentiation, so in those languages −3² will be interpreted as (−3)² = 9. This does not apply to the binary minus operation '−'; for example in Microsoft Excel while the formulas =-2^2, =-(2)^2 and =0+-2^2 return 4, the formulas =0-2^2 and =-(2^2) return −4. (edit) Digging into this a little bit more... https://www.gnu.org/software/bc/manual/html_mono/bc.html#TOC13 https://www.gnu.org/software/bc/manual/html_mono/bc.html#TOC... The expression precedence is as follows: (lowest to highest) || operator, left associative && operator, left associative ! operator, nonassociative Relational operators, left associative Assignment operator, right associative + and - operators, left associative *, / and % operators, left associative ^ operator, right associative unary - operator, nonassociative ++ and -- operators, nonassociative This precedence was chosen so that POSIX compliant bc programs will run correctly. This will cause the use of the relational and logical operators to have some unusual behavior when used with assignment expressions. Consider the expression: ... This brings us to the POSIX specification for bc https://pubs.opengroup.org/onlinepubs/9699919799.2018edition/utilities/bc.html https://pubs.opengroup.org/onlinepubs/9699919799.2018edition... This also shows the unary - having higher precedence than ^. https://github.com/gavinhoward/bc/blob/master/manuals/development.md https://github.com/gavinhoward/bc/blob/master/manuals/develo... This document is meant for the day when I (Gavin D. Howard) get hit by a bus. In other words, it's meant to make the bus factor a non-issue. This document is supposed to contain all of the knowledge necessary to develop bc and dc. In addition, this document is meant to add to the oral tradition of software engineering, as described by Bryan Cantrill. ... now, it would be interesting if gavinhoward could clarify some of the design thoughts there (and I absolutely love the oral tradition talk).

jasinjames 2y ago

I had a similar issue just two weeks ago at $DAYJOB. I was scraping a log file and I had written an awk script to convert the HH:MM:SS.uuuuuu timestamps to nanoseconds since epoch for ingestion by another tool. Little did I know that awk uses floating point for numbers internally, and so my conversion function was incorrectly rounding the result, which messed up my later analysis in arcane ways. To add insult to injury, I had the same problem again when paging through the data in Visidata, but there was an easy workaround I don't exactly remember.

smartmic 2y ago

The problem with Excel is that it tries to do almost everything in one software tool: front-end (editing, presentation), analysis, semantic modeling, data storage/database. Almost all reasonable engineers see that there is something wrong with such an approach. But almost all everyday computer users think that this is the way computing has to be. Sometimes I wonder why even I voluntarily open it for certain tasks - anyway, despite all the criticism, Excel has reached the Lindy[1] threshold for me and is here to stay. [1] https://en.wikipedia.org/wiki/Lindy_effect https://en.wikipedia.org/wiki/Lindy_effect

ravetcofx 2y ago
Except that it's proprietary and will eventually be un-maintained and stop working. But spreadsheets, in general, fall under the Lindy effect and open source software will continue it for centuries to come.
- Closi 2y ago
  Although excel spreadsheets have had more longevity than lots of other formats…
- Dalewyn 2y ago
  You might have had a point in the days of .xls, but Excel by default uses .xlsx now which is just XML packaged inside a .zip archive if I recall. And yes, Excel still fully supports .xls too. I fear whatever format LibreOffice uses will die first, case in point I don't even remember what it's called even though I should as a computer nerd.
  - wizzwizz4 2y ago
    MS-OOXML is barely an open format. Have you tried implementing it? ECMA-376 part 1 is over 5000 pages, and there are four parts to it! (Part 1 contains an extra bit about SpreadsheetML, but by that point we were two zips deep. I turned back ere I got lost.) The OpenDocument formats, meanwhile, are older, simpler and better than their MS-OOXML equivalents. (The ODF spec is 1041 pages altogether – 215 pages of that are the spreadsheet formula language.) LibreOffice's implementation is a little janky, sure, but I can edit OpenDocument files by hand. Try doing that to a MS-OOXML file. (Good luck.)

darkhorn 2y ago

This is why Statisticians do not use Excel for scientific work. They use SPSS, R, Minitab, MATLAB, SAS, etc.

antegamisou 2y ago
Yeah I was about to say that the news has to be that Excel has extensive use in biomedical research.

Eddy_Viscosity2 2y ago

But why oh why can't you disable automatic date reading? Put something in settings with a checkbox that I, the user, can tell excel to F-off with that nonsense. Is there anybody who can argue the 'for' case for having this on all the time without recourse?

simonh 2y ago
Aside from setting all your cells to text, they have added checkboxes for this now. https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac https://insider.microsoft365.com/en-us/blog/control-data-con...
- Eddy_Viscosity2 2y ago
  I just tried this out. I'm using office 2021 and when I first opened excel, this setting was there just like in the link and it seemed to work when I entered a few strings in. 'Fantastic!' I thought. Then I closed it and thought of a few other date-like strings to try and this time the option had disappeared! Every date-looking string was instantly turned into date! I tried a few other times and this setting is gone. WTF is that about?
  - simonh 2y ago
    Have you opened an older file? Maybe it only works with files created with the feature. I’ve no idea why that would be the case, but it’s all I can think of.
    - Eddy_Viscosity2 2y ago
      Both times (and a few after that), it was accessing excel settings before opening any files or starting a new one. I've tried after opening files, same thing. The setting is just not there like it was the first time.
XlA5vEKsMISoIln 2y ago
Introducing checkboxes somewhere in the bowels of settings adds more ways for things to go wrong. What is the default? Do the defaults differ across versions? How do you keep it consistent across computers and installations? What if you actually need the function ad hoc? This reminds me of CSV export. I haven't used Windows for a decade but I remember that if you wanted to change how decimal numbers were exported you had to change the locale and reboot the computer. To change a setting in Excel. That is insane. Sprinkling checkbox patches isn't too far from this.

bluedino 2y ago

Missing checkbox. "Assume Everything Is A String" We used to hit all kinds of Excel weirdness with inventory etc. It was our fault that our part numbers could look like this: 00010190-95.020

alexlll862 2y ago
That feature already exists. It's technically not a "checkbox", but you can set a cell (or all of them) as string type in like 3 clicks.
- Arrath 2y ago
  Still, an easy footgun to stumble into if you weren't mindful when setting up your spreadsheet
- epistasis 2y ago
  The problem was that you could not import the data without it being destroyed. By the time you could see it in Excel, the conversion had already been completed, without a way to get text.

databoy24 2y ago

Excel will convert many CUSIPs (strings) into numbers (scientific notation). For a relatively comprehensive list, see below. https://www.empirasign.com/cusip-excel-rosetta/ https://www.empirasign.com/cusip-excel-rosetta/ Barring some types of corporate actions, CUSIPs numbers cannot change, and I doubt the ABA is aware of this issue.

bvan 2y ago

Sounds like user-error, not a software error. Learn to use the software and its idiosyncrasies. RTFM.

AllegedAlec 2y ago
God I hate programmers so much. "Hurdurdur it's not my fault. The end users are wrong. We shouldn't have to spend time creating an interface and UX that actually works as expected" Absolute fucking retards. We make tools for these people. If these tools do not work as end users would expect to that is our mistake. Stop coping about 'training end users'.
- Biganon 2y ago
  This. There is absolutely no excuse for Excel's behavior here. It's been DECADES and they still haven't added a goddamn dialog that says, upon importing data, "OH, it looks like your data contains stuff that looks like dates, are they dates, or are they NOT DATES? Thanks, have a nice day"
  - naught0 2y ago
    This does indeed exist in the latest version I have running. When opening a CSV all automatic conversion can be disabled
- ozim 2y ago
  That's not programmers, that's business analysts, product owners, quality assurance, programmers only code what is asked for. Having score set straight - not everything can be made "just do the UX that actually works" because there is more users and more "what actually works" than you can implement. Not everything can be "just simple", excel for instance is powerful beast but it is powerful because it is complex and one can do really complex stuff with it. I can make simple spreadsheet software but no one will be using it because it will not allow to do really complex stuff.

a-dub 2y ago

kinda like automatic fallback or other automatic help/fix features. they sound like something that would be helpful but in practice they just end up being a massive violation of the principle of least surprise.

codr7 2y ago

At least we got rid of Access? I've seen things created in MS Access that can't be unseen.

__mharrison__ 2y ago

One wonders if these scientists took a week to learn sufficient Python and pandas, how much better the world would be? (Certainly possible, I teach "I don't want to be a programmer"-types all the time. Taught a class this week in fact.)

ClassyJacket 2y ago
I work with this type of scientists, supporting them in an IT capacity. Many of them do try to learn Python, I've been quite impressed by it, but realistically Excel isn't going away any time soon regardless. Sometimes it's the right tool for the job.

aaron695 2y ago

[dead]

jtrueb 2y ago

Quit using Excel in research!

deleted 2y ago

[deleted]

jimnotgym 2y ago

Fixing the headline... One in five genetics papers have errors caused by mistakes in the use of Excel As if longhand calculations never have errors?

remus 2y ago
The difference here is that the excel errors are systematic so should be more easily avoidable, whereas long hand calcs are more varied and harder to correct.
- jimnotgym 2y ago
  I use Excel a lot. These things don't trip me up in my field. Could it be that this field is a rather niche use case, and therefore the users should learn to watch out for them?

stellalo 2y ago

> The researchers […] say the issue can be fixed by formatting Excel columns as text and remaining vigilant—or switching to Google Sheets, where gene names are stored exactly as they're entered. I think the safest fix is to avoid spreadsheets altogether, as long as scientific research is concerned.

v1ne 2y ago

We have a saying in German: A bad worker always blames their tools. Excel has many quirks, but I'm still very grateful that it exists, for quickly putting together some numbers and still being able to change the inputs to my formulas.

AStonesThrow 2y ago
That's interesting. In English, the idiom is: "It's a poor artist who blames his tools."
- thebytefairy 2y ago
  As a native English speaker, I've actually only heard 'a bad workman blames his tools', not the artist form. Now that I google it, did you mean craftsman? I don't see any references to artist.
hennell 2y ago
A poor worker blames their tools, but no-one can write the Bible on a stamp with a carrot...
augustk 2y ago
I think what you mean here is that you are grateful that spreadsheet applications exist. https://simple.wikipedia.org/wiki/Spreadsheet https://simple.wikipedia.org/wiki/Spreadsheet
asjir 2y ago
In Polish we have: a bad ballerina is hindered by the hem of her skirt
jyunwai 2y ago
But at the same time, there is a saying in user experience design: don't blame the user. At least at the time of the article, there was no way to disable the auto-conversion of certain strings (like "SEPT2") into dates. A setting to disable this would have stopped many errors amplified by researchers working late at night or rushing to meet a deadline. It's true that there has to be some point where the users of the tool need to put in the effort to learn how to best use it. But effort poured in from the other end by the developers, too, can go a long way to prevent common errors and save users time.

zelphirkalt 2y ago

Know your tools. When I see such headlines, I feel sad about the state of this world and obstacles to progress, angry about the stupidity of designing all the tools for the most lazy of users, and at the same time I also laugh about the failures of proprietary software and its users, who could easily have invested a little bit of time to learn alternatives, but apparently chose not to. Yes the tooling they use might be terrible. It is your responsibility to either deal with the terrible tooling yourself, learn better tooling, or get a capable computer person in the room, who can navigate the tooling landscape and get you the results. And of course, that is not even addressing checking your result yet. This is a sad state of the research landscape, often financed by public money, and then throwing money at MS for using a proprietary tool and messing up.

dmvdoug 2y ago
Yes, tools are not made for users but for the tool-builders. Everyone else must adapt!
- itchyjunk 2y ago
  A single tool must work for ever use case or should the tool be picked based on the use case? Tool X doesn't work for use case Y. Regardless of where you shift the blame, tool X doesn't work for use case Y holds.
- CivBase 2y ago
  Excel is made for spreadsheets, not databases. Microsoft already has a database application in Access.
- brookst 2y ago
  I think you’re reframing “when all you have is a hammer, every problem is a nail” into “when all you have is a screw and a hammer, it’s outrageous the hammer wasn’t designed to drive screws”.
- mrgoldenbrown 2y ago
  Tools are designed for a particular use case/userbase. Excel was not designed for gene scientists. It wouldn't make sense for me, a home owner who uses a jigsaw to cut molding, to complain to a chainsaw maker that their chainsaw isn't right for my job.

nuc1e0n 2y ago

A HN thread similar to this one about how using Microsoft Excel corrupts data occurs about once a month. Nothing ever comes of it. Those emotionally invested in using Excel actively ignore criticism of it, or themselves for that matter.

jsmith99 2y ago
Actually Microsoft have recently added a warning dialog about certain automatic csv conversions. Perhaps they finally read the thread. https://insider.microsoft365.com/en-us/blog/control-data-conversions-in-excel-for-windows-and-mac https://insider.microsoft365.com/en-us/blog/control-data-con...
- nuc1e0n 2y ago
  Yes I'm aware. It would be good to have a group policy or some such registry setting to disable them all by default for every computer on the network.

wodenokoto 2y ago

What do you guys recommend for viewing and scrolling around tabular data? Most of my excel usage is literally just double clicking to have a look at a file.

gverrilla 2y ago

The problem with most software for complex tasks, including Excel and Python, is the hidden and/or non-intuitive mechanics. That's why a AI companion should accompany them for non-expert users (99%), which would be able to check what the user is doing for intention-result errors and warn.

huijzer 2y ago

No, it is because incentives are misaligned and people care about getting cited. It doesn’t matter whether it’s correct or not.

lbriner 2y ago

Don't get me started on the automatic conversion of strings that it thinks are in scientific notation into numbers - which you can't switch off! We have large data exports from systems that include things like unique location code. You accidentally happen to notice that a block of these look weird and it isn't just the display of them that has changed, the contents of the cell were changed by Excel automatically, without asking, and you cannot disable it. Absolute BS after all these years. I hate that they won't fix these niggling issues that keep tripping people up over the years and just make excuses. Microsoft's usual response is: "We only work on things that affect a large number of customers". Yeah Microsoft, if you keep closing these bug reports, then each time someone reports it, you can just say that it only affects one person and close it again. Or...you could show how amazing your company is by doing what most of us have to do: Fix it, add more debugging for the next time it happens if you can't recreate it, or have a properly tracked reason to say, "only a very few people have asked for this but changing it might break these other areas/bacwards compatability" or something.

tssva 2y ago
Is there a reason you don't specify the data type when importing or if it is a frequently used data file format automate the import using VBA specifying the correct data type?
- mark-r 2y ago
  If you open a CSV file without going through a specific import process, you don't even get the option to specify a data type. And once it's open it's too late to fix it, the original data is already gone.
  - tssva 2y ago
    The specific import process isn't some sort of esoteric process. It is the data import wizard. Also as I said if you are regularly importing data from a file with the same format writing some VBA to do so is pretty quick and simple task. Also your data isn't gone. It is still in the CSV file you imported it from. Re-import it.

dugmartin 2y ago

US ZIP codes are what I always have issue with during imports. They start with 0 in New England and the zero always gets removed.

moi2388 2y ago

The problem is scientists insisting on using tools like excel and Python instead of decent type safe language to do their calculations in

mixedbit 2y ago

Does Excel have some reasonable way of unit testing of formulas?

nhatcher 2y ago
No, as far as I know. And I have been thinking of ways to that fir the better part of the last decade. I think bringing modern programming practices into Excel might make huge differences. But I don't know how to do that in an Excel user friendly way

temptemptemp111 2y ago

[dead]

amai 2y ago

Thank got we don't have a monopoly in office software and can easily switch to a competitor.

ThinkBeat 2y ago

This seems to be a problem quite smilar to dynamic or static typing. The users expect that Excel will figure out what to do with the input correctly. But you can manually tell Excel what to do with data in a column.

benrutter 2y ago

I know that the "anything" goes nature of excel is part of what makes it such a universally reached for tool, but every time I see sonething like this, I think that there would be a clear use case for a more restrictive spreadsheet. 9/10 spreadsheets are tables, but because they're spreadsheets they inherit the behaviour of "no conistent behaviour in columns, everything is independent and different".

zamadatix 2y ago
You can set the format of the column when making a table. Much more than that and you've ended up at Access. The problem here with Excel is it's wonky default until you've made that explicit choice.

BurningFrog 2y ago

This is 100% on the scientists who continue to use a tool that doesn't work for their use case!

ttoinou 2y ago
Maybe they are locked into the tool because of legacy reasons, and they don’t have a developer around in their team responsible for upgrading their tool / software workflow. This must be a non-trivial problem for a lot of workers all around the world

oglop 2y ago

lol. What a solution. Maybe learn how to use a proper DB. Research science is like 14 years behind the times. I was amazed at the SciPy24 conference how almost no data scientists use DBs. Wild. Pandas is your friend here and can at least help you with json stuff. Using excel though, yeesh. Yeah the problem isn’t excel, it’s bad training on data handling from schools.

me_vinayakakv 2y ago

Reminds me of YAML's handling of booleans (`no` -> false) and git commit SHAs (treated as numbers in scientific notation)

robblbobbl 2y ago

lol

fifilura 2y ago

I always felt that there is a hierarchy for working with numbers that goes something like this Ease of use: 1. Excel 2. SQL 3. Functional programming (e.g. Scala, Python to some measure e.g. Pandas) 4. Imperative programming (C/C++/Java) But then there another hierarchy that (roughly) goes in the other direction, which is about quality, repeatability, tooling. If you are at 1 or 2, you responsibility will not be about writing tests and verifying your code using traditional engineering methods. However! You are responsible for cross checking your results based on the input. This may be a manual process. But actually looking at the numbers from several different angles can give higher quality than writing contrived testcases (in 3 or 4).

anthk 2y ago
2 s,SQL,j https://www.jsoftware.com/indexno.html https://www.jsoftware.com/indexno.html Also: https://www.jsoftware.com/help/dictionary/intro.htm https://www.jsoftware.com/help/dictionary/intro.htm EDIT: The help section has 6 books. If you want, you can do self-teach yourself advanced math stuff with very few lines. I suggest to install Gnuplot as a dependency, for plots.
- fifilura 2y ago
  Very good! I believe you. Unfortunately I feel like it would be irresponsible to transition our stack to working with J because of available competence and relearning. I can see it being used in research though.

om8 2y ago

Younger me would've said that WYSIWYG editors were a mistake and that researchers should've used LaTeX. Now I think these errors are a small price to pay for convenience. One could waste a lifetime fighting small things like this and still lose. It's just the world we live in.