Live data from Hacker News

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

science.org

51–60 of 267 posts

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

#51
post #37

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.

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

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.

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

#52

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.

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?

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

#54

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 - a…

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.

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

#55

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 resul…

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.

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

#56
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?

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

#57
post #55

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 resul…

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…

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.

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

#58
post #55

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 resul…

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…

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

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

#59
post #18
post #11

Earlier quoted context omitted.

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.

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.

It's not like you have to be in genetics for excel to bite you. I once had a “business” column in a CSV with one cell set to July 11. Why? Because the business was 7/11. (I assume in a different locale it would've become November 7?)

The simple solution is to do what every CSV → DataFrame library does, which is ensure columns are a homogenous type. In this case a single non-date entry in a column would be enough to treat the whole column as string.

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

#60
post #18
post #11

Earlier quoted context omitted.

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.

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.

Perhaps the first time you start excel maybe a short setup tutorial that sets the default parameters you want for every spreadsheet you create.
Post reply on HN