Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

science.org

Post reply on HN
Print view View original

71–80 of 267 posts

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

#71

Post by dehugger »

tengwar2 wrote: post #47
chucksmash wrote: post #26

Earlier quoted context omitted.

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.

But if you actually do know what you are doing, you know to set the cell type appropriately.

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.

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

#72

Post by shermantanktop »

smartmic wrote: post #50

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…

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.

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

#73

Post by alexlll862 »

bluedino wrote: post #62

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

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.

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

#74

Post by [deleted] »

Prickle wrote: post #31
prepend wrote: post #15

Earlier quoted context omitted.

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.

They already did in 2023. Excel did not have an option for turning automatic conversions off. You can now, FINALLY disable automatic conversion. Honestly that "feature" has been a bane of my existence, and I don't work with genes.

[deleted]

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

#75

Post by philipwhiuk »

smartmic wrote: post #50

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…

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'

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

#76

Post by shagie »

bikenaga wrote: post #66
shagie wrote: post #58

Earlier quoted context omitted.

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

Thanks for looking all this up, particularly the stuff about spreadsheets. I wonder what real early spreadsheets like Lotus 1-2-3 or even Visicalc would do with "-3^2". I have to feel one of the early spreadsheet programmers just made a mistake, because I can't think of a practical reason for going against the mathematical convention if they were already aware of it. Or maybe it comes from bc? ... does bc predate mod…

You've got to go even earlier... which is where the bc link becomes interesting. It's from 1975.

Lotus 1 2 3 dates from 1983... I can't find a copy of it that is runnable.

VisiCalc would be a good one to look at at 1979. It also presents 9 https://archive.org/details/VisiCalc_1979_SoftwareArts

You've also got sc https://en.wikipedia.org/wiki/Sc_(spreadsheet_calculator) from 1981.

    docker run -it ubuntu:latest
    # apt-get update
    # apt-get install sc
    # sc
    = -3^2
And you'll see 9.00 (screen shots of those two https://imgur.com/a/L0ZvJlP and the one from VisiCalc )

This is the way its worked for a long time.

---

(edit / further thoughts)

I believe that the underlying issue is that unary - (negation) and binary - (subtraction) use the same operator and you need the unary one to have a very high precedence to avoid other problems from happening.

Consider the expression: 2^-2

Is that 0.25 or a parse error?

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

#77

Post by databoy24 »

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.

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

#79

Post by bc_programming »

bikenaga wrote: post #48

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…

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.

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

#80

Post by kazinator »

bikenaga wrote: post #48

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…

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.
Post reply on HN