One in five genetics papers contains errors thanks to Excel (2016)
science.org
science.org
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.
Also your data isn't gone. It is still in the CSV file you imported it from. Re-import it.
Writing some VBA is a simple process if you're a programmer. I wonder how many genetic researchers fit that description?
P.S. when I said "too late to fix it", I meant by some process within Excel. Of course you can re-import the original file, but maybe you only notice the problem after you've done a lot of work with it?
Do you/have you worked in a corporate environment? You seem to have an idealistic view about how end users are expected to use Excel.
The original comment I responded to said they regularly imported large data sets and the in the case of the genetists they also are regularly importing data into Excel. In other words Excel is a regularly used and fundamental tool to their work. In this case I would expect someone to learn the basics of using it. Just as I would expect a developer to learn their editor, build system, version control system, etc.
The fact is the person was double-clicking a file in a list to view its contents and Excel was trampling it. Nobody in their right mind will waste time to open Excel first, use import feature, re-navigate to the file they were already looking at, and go through the import dialog just to see what's inside.
Every week it bites me once or twice. Drives me bananas.
> 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
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.
Bioinformaticians are not doing their analyses in excel.
-3^2
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.
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.
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.
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 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.
(- (expt 3 2))
(expt -3 2)
Likewise, in dc
3 _ 2 ^ p
_3 2 ^ p
3 2 ^ _ p
When you start touching infix, you get into https://en.wikipedia.org/wiki/Shunting_yard_algorithm which was not a fun part of my compiler class.
(And yes, I do recognize your credentials ... I still think that lisp and forth (above examples for dc) are better notational systems for working with computers even if it takes a bit of head wrapping for humans).
3 _ 2 ^ p gives 0
_3 2 ^ p gives 9
3 2 ^ _ p gives 0
5 _ p gives 0
_5 p gives -5
~ % dc -v
dc 6.5.0
Copyright (c) 2018-2023 Gavin D. Howard and contributors
Report bugs at: https://git.gavinhoward.com/gavin/bc
This is free software with ABSOLUTELY NO WARRANTY.
~ % dc
3 _ 2 ^ p
9
_3 2 ^ p
9
3 2 ^ _ p
-9
5 _ p
-5
_5 p
-5
(control-D)
dc -V
dc (GNU bc 1.07.1) 1.4.1
3 _ 2 ^ p
0My dc does have a few differences from the GNU dc. I added the extension of using _ as a negative sign.
That is why you are both seeing behavior differences.
You are correct about its behavior.
See https://git.gavinhoward.com/gavin/bc/src/branch/master/manua... (scroll down to the underscore command).
Postfix is interesting in forth. It makes the stack manipulations very easy to reason about, and the stack is very important there so this looks like a win. The cost is in coherently expressing complex functions, hence the advice to keep words simple. The forth programmers are doing register allocation interwoven with the domain logic.
Lisp makes semantics very easy to write down and obfuscates the memory management implied. No thought goes on register allocation but neither can you easily talk about it.
Discarding the lever of syntactic representation is helpful for communication and obstructive to cognition. See also macros.
(Well, I just tried "=-3^2" in an org-mode table and it gives "-9".)
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
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?
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).
There really isn't a design choice to be made. POSIX requires unary negation to have higher precedence.
The only precedence change (I can remember) from GNU bc is that I changed the not operator to have the same precedence as negation. This was so all unary operators had the same precedence, which leads to more predictable parsing and behavior.
Was it a "this is the way that bc worked in the 70s because it was easier to write a parser for it?" or was there some more underlying reason for the "this problem gets really icky if unary negation has lower precedence than the binary operators and makes for other expressions that become less reasonable?"
It's like the Logical XOR issue ( https://youtu.be/4PaWFYm0kEw?t=2236&si=Wi0gwV-XctLGN98I ) ... and I'm of the opinion that there's a real reason why this design choice was made.
(Aside: Some other historical "why things work that way" touching on dc's place in history: Ken Thompson interviewed by Brian Kernighan at VCF East 2019 https://youtu.be/EY6q5dv_B-o?si=YKr4j_FAEp-OihiX&t=1784 - it goes on to pipes and dc makes an appearance there again)
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".
You said that “-3” = “0-3”.
So we have “-3^2” is “(0-3)^2” is 9. Agreeing with -3^2 = 9.
You’re performing a sleight of hand when you define “-3” to be “0-3”, but move the parenthesis to get your second equation. You have to insert your definition as a single term inside parenthesis — you can’t simply remove them to change association (as you have done). That’s against the rules.
So if you think “-3” is “0-3”, then you should agree the answer is 9.
It’s entirely unambiguous due to the parenthesis.
I don’t think the rest of your argument actually makes sense.
There is no sleight of hand required. The original argument is entirely related to having unary minus and binary minus which are different operators conceptually have similar precedence as being less surprising.
When you try to swap in the unary operator without that to make it “less surprising”, you get 9.
Precisely what you said was wrong about the unwary operator (in Excel).
> The original argument is entirely related to having unary minus and binary minus which are different operators conceptually have similar precedence as being less surprising.
And no, you don’t get 9 when you swap the unary operator. That’s the whole point and why it’s surprising that Excel did reverse the precedence for implementation easiness.
No, I didn’t.
There is indeed two ontologically different elements 3 and (-3) in Z. The question is however purely about what is the meaning of the ambiguous without precedence rules representation -3^2.
Note that it gets more complicated quickly if you want to keep thinking about it in that mathematicians often consider ontologically different but equivalent operations as the same when it’s irrelevant to what they are doing or the results trivially extend to both case. See for example 3-3 and 3+(-3).
But if we’re doing math mostly on computers, we should adopt rules that make writing on computers easy — not pedantically insist typing code follow the rules of handwriting polynomials.
Not to unduly slight your teacher, but it could be they weren't sure about what "-3²" means. Everyone who teaches has gaps in their knowledge -- I sure did. :-)
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.
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.
That's true, if there are no angles greater than 90° or less than 0°, as is the case in a non-pathological right triangle. In this case, as ratios of nonnegative lengths, all trig functions are always nonnegative.
If you want to include angles outside those bounds, then you care about what exactly occurs where, and while you can unambiguously define angles between 0 and 90 to have all positive trig functions, you can also unambiguously define them to have negative sines and tangents. Fundamentally what's happening is that you're defining certain line segments to have negative length instead of positive length. Which line segments should have negative length isn't a question about angles.
> You can decide to define the functions differently, but then they'd no longer be the sine and cosine, they'd be something else.
Only in a sense much stricter than what people generally use. Sine and cosine themselves are hard to distinguish - you can also call them sine (x) and sine (x - 270). Some people might argue that the sine of (x - 270) is still a sine.
> In general, the two functions can be described by their differential equations
If you do that, you'll completely lose the information about where sine is positive and where it's negative. You can apply any phase shift you want (as long as you apply it to both functions) and their differential equations will look exactly the same.
You could define trig functions differently, but then you'd need a separate pair of unnamed functions to express "the ratios of unsigned side lengths of a right triangle in terms of its unsigned interior angles". It's the same reason we don't count "-1 apple, -2 apples, -3 apples, ...". Or why horizonal and vertical lines usually fall on the x-axis and y-axis instead of the (1/√2,1/√2)-axis and (-1/√2,1/√2)-axis. We optimize for the common case.
> If you do that, you'll completely lose the information about where sine is positive and where it's negative. You can apply any phase shift you want (as long as you apply it to both functions) and their differential equations will look exactly the same.
What do you mean? "sin(0) = 0, cos(0) = 1, and for all x, sin'(x) = cos(x), cos'(x) = -sin(x)" is perfectly unambiguous. If you changed the initial conditions, you'd get another pair of functions, but then they'd no longer be the sine and cosine, they'd be some other linear combination. And for that, refer to what I said about the x-axis and y-axis: better to take the stupid simple (0,1) solution and build more complex ones from there.
It's pretty straightforward. "sin(0) = 0" is not a differential equation. Any phase shift applied to sine and cosine will produce exactly the same set of differential equations that apply to sine and cosine; you can rename the shifted functions "sine" and "cosine" and you'll be fine.
Bearing is a nautical convention not a mathematical one.
I have worked on boat computer systems and can assure you that all the angles were in radians going in the proper direction while beatings were separate always shown in degrees and clockwise.
> There isn't really one mathematical convention on "angles".
There is for angles in the plane, which are the angles I was discussing. In every math course from trig where people first encounter angles in the plane they increase as you go counterclockwise. This is true in trig, precalc, calculus, ... You will not find a math textbook in which plane angles increase clockwise. I think that counts as a convention.
That convention determines the graph of the sine function, because sin theta is defined in trig courses as the y-coordinate of the point where the ray from the origin determining the angle intersects the unit circle. So (e.g.) if 45 degrees means 45 degrees clockwise, that ray is below the x-axis, and the y-coordinate of the intersection is negative -- and hence, sin 45 degrees would be negative.
If angles increase clockwise from the positive x-axis, then sin 45 degrees will be negative. And if sine 45 degrees is negative, then angles are increasing clockwise from the positive x-axis. And any mathematician would tell you that sine 45 degree is 1/sqrt(2), not -1/sqrt(2).
> ... in a math class it's normal to orient phi in whatever way makes sense to you.
You're correct that there are two prevailing conventions for the angle phi in spherical coordinates. Mathematicians measure phi downward from the positive z-axis, so it takes values from 0 to 180 degrees. (Actually, it's sort of like "bearing" that you mentioned.) Physicists measure phi upward from the x-y plane, so it can take values from -90 to 90 degrees. It does cause some confusion in teaching Calc 3, because students also taking a physics or astronomy course may be seeing two conventions for phi. However, in 3 dimensions (spherical coordinates) there's no natural "clockwise" or "counterclockwise".
But there is a convention for measuring phi in math classes -- it's the one I described above. Check any calculus book. Our colleagues in physics don't like it, but oh well. :-)
I think it's a fairly common setup for all 2D graphics software.
I actually didn't even think about it until now. Now it's going to bug me. God damnit. :V
It's the only image format I've ever seen that does that -- everyone else stores lines in top-to-bottom order, consistent with putting (0,0) at the top-left.
Is that because the electron beam in cathode ray tubes scanned from top left to bottom right?
I think it’s interesting that actually, they didnt change the rotation definition (from X+ toward Y+), but because it’s a visible change from their inversion of the plane, people believe they did.
But even if there's a lot of agreement that an existing convention could stand improvement, that doesn't by itself make it "a good reason any time" for throwing out the existing convention.
What is a "convention"? It's something followed by a large "installed base". So changing a convention means a large cost will be incurred in changing up.
Who should decide whether the benefits of changing outweigh the costs? Someone has to pay for it, and simple fairness suggests that the people who will bear the costs of changing up should have the largest say.
The point is that just because someone thinks something new is better doesn't mean that old should be thrown out. And if you ignore that installed base, the change just doesn't happen.
We tried in the U.S. to switch over to metric years ago. Many of us think it would have made sense, but many more people didn't agree and it didn't happen.
It would be easier computationally if there were 100 degrees in a circle rather than 360. But the 360-installed-base is too large and the costs of changing are judged to be too great, so we're stuck with 360.
You're absolutely right, though, that suggestions for change should always get a fair hearing, and people who believe in them should go ahead and see if enough other people will sign on.
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
3^(2+2)+5
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.
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.
In Unicode it's U+00F7: https://www.compart.com/en/unicode/U+00F7
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.
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
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
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
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:
There is also no difference in the caret notation vs superscript, its upward pointing form literally meant to signify SUPERscript
It's far better that
0-3^2
-3^2
are consistent, consistency between exponent and addition makes little sense since by universal convention they have different priorities, so you'd not expect any "consistency" there Also your -3+2 example is meaningless since its output is the same as
-((+3)+2)
so there is no inconsistency with
-9
And no, ^ doesn't universally work differently vs superscript, just in some poorly designed apps
While the caret is meant to symbolize superscript, it is nevertheless a completely different notation for exponentiation.
I don't see why 0-3^2 and -3^2 need to be consistent necessarily. Sign change and subtraction are different operations, so they can have different relationships with other operators.
If + worked like you want ^ to work, then -3+2 would equal -5, instead of the more common -1.
^ does work differently from superscript in all apps. The way you write "three to the power two plus two" is completely different.
> While the caret is meant to symbolize superscript, it is nevertheless a completely different notation for exponentiation.
Wait, do you believe slash / to be a completely different notation with different rules for division?
Also, it's not completely different, I've already explained that its form points to the same participle - RAISing base to the power, exactly the same as superscript. It's just that input/typesetting on computers is very primitive, so you can't really use superscript conveniently, otherwise it's semantically the same, so having different rules for the same meaning makes no sense
> I don't see why 0-3^2 and -3^2 need to be consistent necessarily
ok, if you fail to see this basic similarity but somehow think -3+2 is identical, don't have anything else to say here
> If + worked like you want ^ to work, then -3+2 would equal -5, instead of the more common -1.
Why would I ever want addition to work the same as exponeiation??? That's your weird wish for them to behave the same, I respect the math precedence of operators.
> ^ doesw differently from superscript in all apps
that's not true, https://www.wolframalpha.com/input?i=-3%5E2
Many calculators / calculator apps also behave the same
And of course / is completely different from fractions too. Math notation is two-dimensional, and requires relatively few parentheses. Computer notation is uni-dimensional and requires parentheses all over the place.
This is how math notation looks like, try to write this in C/Excel/Wolfram Alpha without parens:
2 + 2
-3 + 4
_____________ = 17
2 + 3Why? This extra condition doesn't help you, and why your link shows nothing, it behaves exactly as I'd expect, ^ is identical to superscript, you're just making an implicit mistake of thinking +2 is somehow covered by ^ and would be part of the superscript, but it wouldn't, that's a different source of ambiguity
What would help is an example where parens aren't needed, but nonetheless slash would mean something else vs horizontal line, like in the original example
That's how you show semantic "completely different"
The fact that the computer ^ requires parens in more cases like -3^(2+2) is irrelevant for this and doesn't allow you justifying different precedence rules (and your downgrading from "completely different" to "different" isn't a proof, just "tautology". Hey, they also look different, so they are different!)
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.
3 2 ^ - -9
3 - 2 ^ 9
Sigh. I understand why we commonly enter math on basically a teletype-with-ASCII, and I don’t have an urge to go all APL, but for a while we were so close to a future where we could’ve had separate negation or multiplication or exponentiation symbols that might’ve removed so much room for error. I mean, that little calculator and its predecessors were popular and widely used by the same people who brought us things like Unicode and the space cadet keyboard. If only one of them had said, gee, it sure would be handy to have a +- key on the keyboard the person in the next cubicle is designing as I have on the calculator on my desk!
But nope, Everything Is ASCII won and here we are. At least programming languages are starting to support Unicode identifier names, which has its own issues but is excellent for non-Latin alphabet users who want to write code in their own tongue. It seems like a reasonably short hop from there to giving operators their own unambiguous names. I can imagine a near-distant future where a linter says “you typed -3. Did you mean ⁻3?”, to the chagrin of programmers still entering their code on teletypes.
> ISO 80000-2 standard for mathematical notation recommends only the solidus / or "fraction bar" for division, or the "colon" : for ratios; it says that the ÷ sign "should not be used" for division
I think these things are way less standardised even on paper than you believe.
I don’t contend we should change things today. I do think if I were personally writing a new programming language from scratch today, I’d likely use different Unicode symbols for different operators, and the accompanying language server would nudge people to use them.
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.
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.
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.
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.)
I say “I shouldn’t” because the off-ramp from a working solution to a proper productized code-based approach can be very painful.
Many tech start-ups are 'replace this thing people do in Excel with a purpose built tool'
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.
three decades ago :)
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.
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.
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.
Is there anybody who can argue the 'for' case for having this on all the time without recourse?
https://insider.microsoft365.com/en-us/blog/control-data-con...
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?
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.
The old behavior
> Do the defaults differ across versions?
No
> 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.
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."
Excel is a wonderful tool. But you need to learn your tools and find out about possible footguns.
It's entirely valid for the school to tell students to avoid easily avoidable pitfalls.
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.
Why then is it the dominating mindset in software design today, and advertised as being the opposite to the mindset that gives you Excel?
It’s like people complaining because sugar gets misused. Or that murderers stab people with knives. The solutions isn’t to “fix” knives.
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.
I remember Excel team writing about why they didn’t have advanced settings to turn it off. I don’t remember the rationale but I’d rather have some switches I can set for the situations where I don’t want it.
Although I do want it on and just check my data types. And for the most part I solved this by opening and never editing in Excel. It seems to be the one hack I’ve gotten coworkers to stick with is “don’t click save” when opening large files in Excel.
I'm not doing anything nearly as special and always have dates import as numbers for whatever reason. Thanks microsoft.
If excel broke 20% of the time, I’d agree. But it rarely breaks. It’s just widely used.
I’ve used Excel for decades. I just set the data types on my columns. The reason Excel does that is because the vast majority of people like it and rely on it. And changing it now will break millions of workflows.
People assume their workflow is super important and worthy of software making special exceptions just for them. There’s an easy solution that people can follow now. Let’s focus on that rather than introducing a “fix” that breaks it for other people.
Excel has thought about this and there’s no simple fix. Nobody is forced to use Excel.
Excel doesn’t break 20% of the time. It rarely breaks. I think you’re assuming that genetics is more worthwhile than the millions of other uses. Think about how widely it’s used and your analogy doesn’t work very well.
If you do it like most software vendors do, by simplifying and removing functionality, you're moving the needle in the wrong direction.
Ideally on the screen UI only those things are shown, that are relevant in the context.
And the context of beginners is very small, so they don't need to see advanced tools they never will use anyway. But for sure it is not the right way to also remove the tools for the advanced users who do need them.
But it is possible to make UIs that can be customized ..
(Certainly possible, I teach "I don't want to be a programmer"-types all the time. Taught a class this week in fact.)
Somewhat recently, some of the more error prone genes were renamed to accommodate Excel. (Ex: SEPT7 -> SEPTIN7)
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
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".
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.
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.
It would be limited to programmers, though.
https://code.visualstudio.com/docs/datascience/data-wrangler
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.
One in five genetics papers have errors caused by mistakes in the use of Excel
As if longhand calculations never have errors?
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).
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.
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.
I dislike Excel for what it does (EUCs, nearly impossible to track changes, etc.) But on the other hand it is an amazing tool.
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.
Excel has been acting this way since before bioinformatics existed. Authors need to use their tools properly.
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.
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.
The point is that Excel works great for 99% of the people and for 99% of the use-cases. I am a heavy excel user, for my financial planning, work, etc. And it pisses me off when I see a column that should be "networkdays" (working days) becoming $ or getting decimals, but hey, you take the bad with the good.
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.
I’d love to know the percentage of corrupted data across all Excel workbooks.
https://www.youtube.com/watch?v=MFzDaBzBlL0
It's not all on the users.
In that sense the excel UI doesn't make sense only for those not used to it. Which might be a nice analogy
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.
I agree. I love Excel. But this attitude only makes sense if we assume one can only fix easy-to-make mistakes by dumbing the software down, which is not true.
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.
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,
I've seen things created in MS Access that can't be unseen.
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.
I think the safest fix is to avoid spreadsheets altogether, as long as scientific research is concerned.
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.
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.
And Sheets isn't "really better", yet gained a noticeable share
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.
(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.)
Newer programs like Google Sheets have better default behaviors. They have free access to it.
"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'.
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.