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

https://www.science.org/content/article/one-five-genetics-papers-contains-errors-thanks-microsoft-excel

by redbell • 2 years ago

407 255 2 years ago

science.org

mbreese• 2 years 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• 2 years ago

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

happytoexplain3• 2 years 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.

TeMPOraL5• 2 years 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.)

nitwit0052• 2 years 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.

marcosdumay• 2 years ago

Yes, the problem is using Excel, not Excel by itself.

golergka• 2 years 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.

rectang4• 2 years 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.

nitwit0052• 2 years ago

This isn't Microsoft lecturing them.

It's entirely valid for the school to tell students to avoid easily avoidable pitfalls.

prepend5• 2 years 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.

TeMPOraL5• 2 years ago

The only way to move the needle is to design systems that are easier to use correctly.

If you do it like most software vendors do, by simplifying and removing functionality, you're moving the needle in the wrong direction.

eviks10• 2 years ago

Do you think the comment is written by Microsoft??? The sloppy researchers are not his users. Another way to move the needle is to place higher value on technical competence.

mobilio2• 2 years ago

Follow up:

https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

rectang4• 2 years ago

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

HenryBemis2• 2 years 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• 2 years 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.

prepend5• 2 years 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.

rectang4• 2 years ago

Oh, come on. The Excel user interface encourages mistakes, as surely as a bicycle with handlebars that steer backwards.

https://www.youtube.com/watch?v=MFzDaBzBlL0

It's not all on the users.

dudus• 2 years ago

I had a different interpretation of the video you linked. It seems to me even a backwards bicycle is perfectly drivable as long as you adjust you mental model.

In that sense the excel UI doesn't make sense only for those not used to it. Which might be a nice analogy

TeMPOraL5• 2 years ago

At least it's a bicycle.

Remember the whole "computer as bicycle for the mind" thing? That didn't happen, the world went in the opposite direction. Software like Excel are the last surviving remnants of the idea of empowering end users to improve their work and lives.

happytoexplain3• 2 years ago

There are three options:

1. Make the software better (very hard on complex systems with GUIs)

2. Ensure everybody knows all footguns (impossible)

3. Don't care about those people (easy)

If we opt for #3, we might as well not even be in software as a profession/hobby. Having such low standards indicates that we don't really care.

TeMPOraL5• 2 years ago

EUCs

Had to search for that one. It seems that EUC here means "end-user computing", and I was shocked to discover it's a pejorative term used by vendors to describe what they consider a problem that needs solving,

rzzzt• 2 years ago

Careless as in they type in "MARCH1" or "SEPT2" in a cell and these get turned automagically into 1-Mar and 2-Sep dates after pressing Enter?

erehweb• 2 years ago

Original paper https://genomebiology.biomedcentral.com/articles/10.1186/s13...

Prickle2• 2 years ago

Previous popular thread, Aug 2020: 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-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/

Gene Updater: a web tool that autocorrects and updates for Excel misidentified gene names

magicalhippo• 2 years 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.

lukan4• 2 years ago

Yes, something like this is on my mind since quite some years as well.

It would be limited to programmers, though.

scrlk• 2 years 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://www.youtube.com/watch?v=5tWJVLF6PuA

redbell• 2 years 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/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• 2 years 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.

lukan4• 2 years 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.

jasinjames2• 2 years 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.

eviks10• 2 years 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• 2 years 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• 2 years 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• 2 years 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?

eviks10• 2 years ago

Unfortunately researchers can't fork the universe with a different set of defaults for this rigorous study you appear

Djdjur7373bb• 2 years 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• 2 years ago

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

bikenaga11• 2 years 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.

mobilio2• 2 years ago

But it's 9!

Check it: 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• 2 years 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.

shagie5• 2 years ago

https://www.wolframalpha.com/input?i=-3%5E2

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

This also shows the unary - having higher precedence than ^.

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

bikenaga11• 2 years ago

No - in a subtle way, you're assuming that the unary minus has precedence, when the point is that it doesn't.

You're right that "thing^2" means "thing times thing", but in "-3^2", what is it that is being squared? To write it, as you did, as "(-3) x (-3)", assumes that in "-3^2" the thing being squared is "-3". But that in turn assumes that the unary minus is done before the square. By the standard mathematical convention, in "-3^2" the thing being squared is "3". So you do "3 * 3", then you negate the result and get "-9".

alpinisme• 2 years ago

How we right negative literals in expressions is the issue. Nobody would seriously think -x^2 = -4 had no non-imaginary solutions. But if we swap out x for a literal number people start interpreting it ambiguously as maybe a negative number exponentiated, but fundamentally it’s the same syntax as before.

cycomanic• 2 years ago

I had a discussion on one of the Nim boards because Nim does the same, i.e. Writing -2^2=4 but 0-2^2=-4 because Nim treats the first as the unary - which takes precedence over exponentiation. I realise that you can argue how it is technically correct (and interestingly several of the people I was talking with couldn't even get my point), but I still argue it's incredibly unintuitive for anyone coming from an regular engineering/science background who wants to do regular work. I would almost argue will exclude it to be used by many due to this.

bikenaga11• 2 years ago

You're right that violating established mathematical convention can be a deal-killer for some kinds of adoption.

A few years ago I was reading the docs for a new programming language, thinking it might be useful in teaching. The docs were well-written and in a beautifully produced book. I got to the chapter on trig functions and discovered that they'd decided to make angles increase clockwise. And there was a graph of the sine function, with the graph below the x-axis from 0 to 180 degrees. And I sadly put the well-written beautifully-produced book on the shelf and haven't looked at it since.

I can see why people would think "angles increase clockwise" is more natural than the existing convention ("angles increase counterclockwise") - it's the way clocks do it, right? Yeah, it's just convention, but when the established convention has been around for at least a century or two and there are libraries full of books and papers which use it, "more natural" still isn't good enough reason to break with it. And it really wasn't necessary to do that for their project.

I'm sure people who do software can think of lots of conventions which may even suck but will never be replaced.

Validark2• 2 years ago

It's insane to me that you'd throw away a whole language, toolchain, ecosystem, and community, just because you disagree with the caret operator not taking precedence over a unary minus. Do they not have parentheses on the keyboards where you're from?

tsimionescu8• 2 years ago

There's no regular engineering convention about the ^ exponentiation operator, because there is no exponentiation operator in regular or engineering math notation. The superscript used for exponentiation doesn't need a precedence level, it has a natural grouping behvaior.

This is most visible in the fact that you never need parentheses around an exponent expression in math notation, but you need them a lot in programming notation. They are just different notations.

Consider in math notation:

   2+2
  3    + 5

Programming notation:

  3^(2+2)+5

Completely different notations in a much more fundamental way than how they treat unary minus.

bc_programming• 2 years ago

This issue seems like partly an artifact of the invented binary operator ^. In math exponents are superscripts and there's no binary operator- it's part of the term. But for text on computers, binary operators ended up being fabricated for the things you couldn't represent directly. The caret is common and was the first one to appear, but doubled multiplication signs is another one. Traditionally, binary operators have lower precedence than unary operators since the unary minus is considered part of the term, so the exponent turning into an operator mucks things up if it's still implemented to adhere to that logic.

You sort of see the same issue with division. The forward slash is a completely invented binary operator since the actual division symbol was often not present- and let's be real nobody uses the binary division operator when writing formulae. It's supposed to represent the dividing line in a fraction, similar to how division is usually represented in a formula as a fraction of two other expressions. It's got lower precedence than anything in either term- but, if you just replace the dividing line with a forward slash to input the formula into a computer, you'll get incorrect results, because it's replacing what is part of a complete term (the division line) with a new binary operator inserted between sets of terms, which is now subject to precedence rules.

fn-mote• 2 years ago

In my country we teach the same rules in math class. Blaming it on “unary vs binary” was a stretch. Next you’re going to blame it on the lexer for producing the -3 as a single term instead of two. (Which WOULD explain something, but… fix it?!)

In my country we use a horizontal line with a dot above and below to indicate in-line division in lower grades. Exactly like the computer /.

It’s not like there was no precedent here.

MrJohz• 2 years ago

I suspect it's more likely an artifact of how the number is lexed. `-3^2` probably gets lexed into Number(-3), Operator(^), Number(2), which results in the aforementioned precedence issues. The reason for parsing the operator with the number is that it makes it easier to handle the case where you just write a negative number as a literal value into a cell.

Source: I've written an Excel clone before. I don't believe it has the same bug, but if it does, that will be how it's crept in.

EDIT: looking at some of the descriptions of the bug, it seems like it happens when handling variables (i.e. cell references) as well, which makes it seem like a pure precedence issue and not a parsing issue. So I've got no idea, presumably someone simply messed up the precedence order.

kazinator• 2 years ago

In the C language, -a*b parses as (-a)*b, but c-a*b parses as c-(a*b).

Unary minus has a higher precedence than binary operators.

You don't notice because the semantics allows the sign to move around, unlike with exponentiation.

But when we throw in edge cases involved in undefined behavior, oops!

  0 - INT_MIN/2 // fine: parses as 0 - (INT_MIN / 2)

  -INT_MIN/2    // not okay: parses as (-INT_MIN) / 2

The INT_MIN value need not have an additive inverse because of a quirk in two's complement.

MaxBarraclough• 2 years ago

The INT_MIN value need not have an additive inverse because of a quirk in two's complement.

For comparison, in Java, these 3 expressions each yield Integer.MIN_VALUE (i.e. -2147483648):

    -Integer.MIN_VALUE
    Integer.MIN_VALUE * -1
    Integer.MIN_VALUE / -1

I have to admit I expected all 3 to throw.

edit On reflection I shouldn't have expected that, I recall reading John Regehr's blog post on the downsides of how Java defaults to wrapping behaviour: https://blog.regehr.org/archives/1401

FabHK• 2 years ago

I recall reading that this was easier to implement in memory constrained machines running Excel back then, and now can't be changed for compatibility.

It has bitten me when I computed the pdf of a standard normal in Excel, invoking exp(-A1^2), say.

Someone made a website (in 2003, it's a bit out of date) tracking this issue:

http://www.macnauchtan.com/pub/precedence.html

eviks10• 2 years ago

But of course it can be changed, you can make it configurable even if you decide the default should continue be this silly mistake "for compatibility"

Validark2• 2 years ago

This is probably the most overly pedantic, nitpicky reason for not using a program I've ever heard.

I'm aware that there are two different conventions on this issue, so I just use parentheses to get the behavior I want.

But, growing up, as the top math student in my class, it never occurred to me that somebody out there wants -3^2 to equal -9, I thought it was just a weird quirk in some calculators/programs. How would you read that expression aloud? I think of it as "negative three squared" so that's why (-3)^2 makes sense to me. Do you say "the negative of three squared"?

In 8th grade, I remember being instructed to type such an expression into the calculator to observe how it does something contrary to what we expect it to. From that moment on, I thought, "Huh, guess you have to use parentheses." It certainly wasn't cause enough to throw out my calculator, let alone tell others not to use it, just because I prefer a slightly different precedence convention.

lionkor• 2 years ago

"minus (pause) three squared"

archargelod• 2 years ago

I think we should agree that standard notation is too ambigious and switch to reverse polish notation:

  3 2 ^ -     -9
  3 - 2 ^      9

No way to misinterpret that!

charlieyu1• 2 years ago

Everyone learnt -3^2=-9 in middle school… it is a very clear implementation mistake

bikenaga11• 2 years ago

This is probably the most overly pedantic, nitpicky reason for not using a program I've ever heard.

If you found learning math easy, you're fortunate. But lots of people find learning math difficult and frustrating, and things which might not have bothered you can be big deals for those folks. If I used a program in teaching which has a convention about basic arithmetic operations that is the opposite of the convention that mathematicians use, it is one more source of confusion and frustration for people.

Student: "You said that -3² was -9, but Excel says it's 9."

Me: "Well, mathematicians use a different convention than spreadsheets."

Student: "So which one should I use on a test? Can we use both?"

Me: "Since this is a math class, you should use -9, not 9."

Student: "How am I supposed to remember that? This is why I hate math ..."

Everyone will weigh costs and benefits differently. There is plenty of good math software out there like Mathematica, R, Geogebra, or maxima. Spreadsheets didn't seem to offer much, and there was this arithmetic convention thing that I knew would be an issue.

I'm sorry if you find it pedantic and nitpicky. I always tried to minimize unnecessary causes for upset, because there were difficulties enough learning math without my adding to them. If you saw people getting extremely angry or in tears because they "didn't get it", I think you'd understand. Math is really hard for some people.

AtNightWeCode• 2 years ago

The exponent operator is just short for the POWER function in Excel. So from that perspective it is the correct behaviour I think even if it is mathematical incorrect.

jasinjames2• 2 years 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• 2 years 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

ravetcofx• 2 years 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• 2 years ago

Although excel spreadsheets have had more longevity than lots of other formats…

Dalewyn2• 2 years 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.

Yossarrian22• 2 years ago

Its easier for me to imagine a world in which AI makes spreadsheets invisible to the modern person than it is to imagine a world in which Excel isn't the de facto spreadsheet.

shermantanktop• 2 years ago

My problem with excel is that I love it too much, even though I know that I maybe shouldn’t. It hits some perfect point between structured and unstructured data that is conducive to just filling in cells and seeing what happens.

I say “I shouldn’t” because the off-ramp from a working solution to a proper productized code-based approach can be very painful.

sitkack• 2 years ago

Spreadsheet are wonderful and powerful, Excel is ass. Love the concept, hate the implementation.

philipwhiuk• 2 years ago

The biggest problem is that the development cost for a solution 'a bit better' than a complex Excel spreadsheet is often stratospheric.

Many tech start-ups are 'replace this thing people do in Excel with a purpose built tool'

HKH22• 2 years ago

Sometimes people need to be saved from themselves. E.g. spreadsheets can have mistakes very quickly when people treat them as databases and start copying columns between spreadsheets assuming that the primary keys (e.g. in one column) are identical while they happily add rows and move rows around.

faresahmed• 2 years ago

Excel can also send mail through VBA scripts, thus satisfying Zawinski's Law[1].

[1] http://www.catb.org/jargon/html/Z/Zawinskis-Law.html

darkhorn• 2 years ago

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

antegamisou• 2 years ago

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

Eddy_Viscosity23• 2 years 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?

simonh2• 2 years 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-con...

Eddy_Viscosity23• 2 years 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?

XlA5vEKsMISoIln5• 2 years 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.

albrewer2• 2 years ago

What is the default?

The old behavior

Do the defaults differ across versions?

How do you keep it consistent across computers and installations?

It should be per-cell, so it's document specific.

What if you actually need the function ad hoc?

Every option in the formatting pane is ad-hoc, this wouldn't be any different.

bluedino• 2 years 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• 2 years 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• 2 years ago

Still, an easy footgun to stumble into if you weren't mindful when setting up your spreadsheet

epistasis• 2 years 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• 2 years 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/

Barring some types of corporate actions, CUSIPs numbers cannot change, and I doubt the ABA is aware of this issue.

bvan• 2 years ago

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

AllegedAlec• 2 years 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'.

Biganon2• 2 years 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"

ozim• 2 years 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.

codr7• 2 years ago

At least we got rid of Access?

I've seen things created in MS Access that can't be unseen.

__mharrison__• 2 years 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• 2 years 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.

a-dub• 2 years 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.

jtrueb• 2 years ago

Quit using Excel in research!

jimnotgym2• 2 years 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• 2 years 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.

jimnotgym2• 2 years 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• 2 years 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• 2 years 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• 2 years ago

That's interesting. In English, the idiom is: "It's a poor artist who blames his tools."

thebytefairy• 2 years 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• 2 years ago

A poor worker blames their tools, but no-one can write the Bible on a stamp with a carrot...

augustk• 2 years ago

I think what you mean here is that you are grateful that spreadsheet applications exist.

https://simple.wikipedia.org/wiki/Spreadsheet

asjir• 2 years ago

In Polish we have: a bad ballerina is hindered by the hem of her skirt

jyunwai• 2 years 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.

wruza2• 2 years ago

Again, this saying has a nuance people often miss.

You don’t blame your tools.

Not all tools. Not tools forced upon by some archaic industrial standard or habit. Not stupid tools you’d never use otherwise but have no choice.

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.

That’s nice, but Excel didn’t invent spreadsheets. It invented adding BS to them and if it didn’t exist, you’d still have WhateverCalc successor available at the moment.

someotherperson• 2 years ago

In Arabic, “the one who can’t dance says the floor is crooked”

XlA5vEKsMISoIln5• 2 years ago

A bad dancer finds even his ballsack annoying.

zelphirkalt• 2 years 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• 2 years ago

Yes, tools are not made for users but for the tool-builders. Everyone else must adapt!

CivBase• 2 years ago

Excel is made for spreadsheets, not databases. Microsoft already has a database application in Access.

itchyjunk• 2 years 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.

brookst• 2 years 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• 2 years 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.

_Wintermute• 2 years ago

Working in this field, I think a lot of the issue is publishers often require small datasets in xls format for paper submission. So someone in the chain (corresponding author, editor, journal staff member etc.) opens the file in excel and saves it again.

Bioinformaticians are not doing their analyses in excel.

nuc1e0n2• 2 years 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• 2 years 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-con...

nuc1e0n2• 2 years 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• 2 years 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• 2 years 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• 2 years 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• 2 years 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.

tssva7• 2 years 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-r3• 2 years 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.

vikingerik• 2 years ago

Yes, it's that last reason. Backwards bug compatibility. There are zillions of recorded macros and VBA scripts and other workflows by non-technical users that inadvertently depend on these behaviors in subtle ways. It's like all Javascript's weird warts, you can't change these behaviors without breaking old stuff somehow. It's true that Microsoft could say that more directly, but of course they'd rather just avoid that argument.

cynicalsecurity• 2 years ago

Why still use MS Office when LibreOffice is freely available?

wruza2• 2 years ago

Does it fix subj-related issues?

lotsoweiners• 2 years ago

Because that is what your work gives you and what all of your coworkers use.

RajT88• 2 years 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!

Every week it bites me once or twice. Drives me bananas.

dugmartin• 2 years 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• 2 years 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• 2 years ago

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

nhatcher• 2 years 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

amai• 2 years ago

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

ThinkBeat• 2 years 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• 2 years 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• 2 years 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• 2 years ago

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

ttoinou• 2 years 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• 2 years 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• 2 years ago

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

robblbobbl• 2 years ago

lol

fifilura2• 2 years 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• 2 years ago

2 s,SQL,j

https://www.jsoftware.com/indexno.html

Also:

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.

fifilura2• 2 years 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• 2 years 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.

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

Related Stories