Excel squares

A colleague of mine just shared something quite difficult to believe but true. Let me start by asking what is -10^2? If your answer is 100 try again - even if you are using Excel to check your calculations.

The default order of operations is to first perform any any power (^) operations, then any multiplication and divisions and finally any summation or subtraction. At least that is how I remember it from school and how I have been using it to develop code for the last 30 years. Ok, I am not that old - let's split the difference and say 15 years. This means that -10^2 is:

-10^2 = -1*10^2 = -1*100 = -100

Try it in Matlab or other programming languages and that is what you will find, except, in excel. Excel actually calculates -10^2 using the following strategy (as far as I can tell ...):

-10^2 = (-10)^2 = 100

The interesting part is that if you try to calculate =100-10^2 Excel will correctly tell you that this is zero. This means that the incorrect order of operation is only affected when you have a negative sign in front of a number, not when performing a subtraction. The order is not only incorrect when you type a number on a cell but also in cell operations. For example, make cell A1 equal to 10. Now, make cell A2 equal to =-A1^2 and the answer will be 100

What are the implications? That the commutative rule might not be always true in Excel. What I mean by this is that

-10^2 + 10^2 is not 10^2-10^2

How to solve the problem? Specifically tell Excel that what you would like to do is =-1*10^2 or =-(10^2).

I might start writing my power operations like this just in case =((-1)*((10)^(2))) (just kidding).

Comments

We are going to need a Merriam-Webster dictionary for arithmetical logic..!

I am stealing this for my Matlab class ;)