I’ve always thought this kind of funny: even people who are “bad at math” tend to have a good handle on order of operations. You know: do the exponents first, then multiplication and division, and then addition and subtraction.
It turns out that Excel violates that standard in one way. Like all things with computers though, our lack of clarity about what we’re doing is part of the problem: GIGO.
So, consider the following:
3-5=-2
You and I look at this and say: “three minus five equals minus two”. That’s correct, but it misses an important subtlety: the two occurrences of minus in that phrase are homophones — they sound the same but have different meanings!
What?
Both of those minuses are (algebraic) operators. But the minus on the left operates on two numbers (i.e., it’s binary), while the operator on the right operates on one number (i.e., it’s unary).
A better way to state that equation would be “three subtract five equals negative two”. That’s correct because subtraction requires you to know both numbers, while negation only requires you to use one.
So, what does Excel do? In order of operations, most people lump negation together with subtraction. Excel does not: it does negation before even exponents.
This can very easily make a difference in your Excel calculations. Consider this
-3^2
If you evaluated this by hand, you’d do the exponent before the minus, like so:
-3^2 = -(3^2) = –9
But, not Excel. Excel says that it makes a difference whether that minus is used for subtraction or negation: subtraction would go after exponentiation, but negation would go before exponentiation. So, Excel does it this way:
-3^2 = (-3)^2 = 9
Wow. There’s got to be a world of hurt in the business world from that one.
