[dead]
One in five genetics papers contains errors thanks to Excel (2016)
101–110 of 267 posts
Re: One in five genetics papers contains errors thanks to Excel (2016)
#102Earlier quoted context omitted.
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-ge…
Unary and binary - can be given different precedence, because there is never ambiguity as to whether you're in front of a unary minus or a binary minus. A binary operator is never encountered at the beginning, or after a parenthesis, or after another binary operator, therefore those are the cases where you'd have a unary - or +.
Rather, the problem is whether -2 is parsed as a numeric literal, or a unary minus followed by a numeric literal (which would only include positive numbers).
Re: One in five genetics papers contains errors thanks to Excel (2016)
#103An 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…
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:
Re: One in five genetics papers contains errors thanks to Excel (2016)
#104Missing 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.
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.
Re: One in five genetics papers contains errors thanks to Excel (2016)
#105An 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 ha…
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.
Re: One in five genetics papers contains errors thanks to Excel (2016)
#106Earlier quoted context omitted.
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.
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 prod…
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.
Re: One in five genetics papers contains errors thanks to Excel (2016)
#107Earlier quoted context omitted.
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…
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…
> I got to the chapter on trig functions and discovered that they'd decided to make angles increase clockwise.
That is an established mathematical convention, called "bearing". https://en.wikipedia.org/wiki/Bearing_(navigation)
> And there was a graph of the sine function, with the graph below the x-axis from 0 to 180 degrees.
But that definitely isn't a convention anywhere; bearing 0 has sine 1.
There isn't really one mathematical convention on "angles". There's a fairly strong one on angles that are named theta, but in a math class it's normal to orient phi in whatever way makes sense to you. As you trace a sphere, do you want phi to represent the angle between (1) the radius ending in your point and (2) the xy plane, as that angle varies from negative pi/2 to pi/2? Do you want it to represent the angle between (1) the radius ending in your point and (2) the positive z axis, as that angle varies from 0 to pi? That's your call. An increase in the angle just means it's getting wider; what direction that requires the angle to grow in depends on how you defined the angle and which of its sides is moving.
Re: One in five genetics papers contains errors thanks to Excel (2016)
#108Previous 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.…
[deleted]
Re: One in five genetics papers contains errors thanks to Excel (2016)
#109Earlier quoted context omitted.
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…
> I got to the chapter on trig functions and discovered that they'd decided to make angles increase clockwise . That is an established mathematical convention, called "bearing". https://en.wikipedia.org/wiki/Bearing_(navigation) > And there was a graph of the sine function, with the graph below the x-axis from 0 to 180 degrees. But that definitely isn't a convention anywhere; bearing 0 has sine 1. There isn't really…
Regardless of relative directions around circles, the sine and cosine between 0° and 90° can be described unambiguously in terms of ratios between side lengths of right triangles. You could decide to define the functions differently, but then they'd no longer be the sine and cosine, they'd be something else. The whole point of having these two named functions is that they're a common ground, independent of whatever idiosyncratic angle measurements are useful for a given problem.
Re: One in five genetics papers contains errors thanks to Excel (2016)
#110Earlier quoted context omitted.
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'
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.
To be fair, there are no good, accessible database tools around for your average non programmer user.
Access tried to be this a decade ago, until MS started to let it die. So now, your only option is basically Excel. There's a reason it's the main thing people gravitate into.