WARNING! Excel order of operations problem...

[swearingen]

I posted this elsewhere, but I wanted to put it here as well to catch as many Excel users as possible.

The order of operations that Excel uses in its formulas is incorrect for numbers raised to a power with a negative sign in front. Examples:

Excel: -5^2 = 25, but it should equal -25.

What's funny is when you type it like this:

-5^2 + 5^2 --> Excel outputs 50 when it should be 0.

Common sense tells you that it should be the same as:

5^2 - 5^2 which obviously equals 0 and Excel agrees, here.


I discovered this problem with Excel when creating a large spreadsheet and it took me two hours to find out why my formulas weren't working. Microsoft even admits that it did it on purpose in its Knowledge Base:

http://support.microsoft.com/kb/q132686/

My advice: use parentheses like it's going out of style!

 

[RLJ]

-5^2 is equal to 25!

-5^2 = -5 X -5 = 25!

When two negative numbers are added together they equal a negative, but when they are multiplied they equal a positive.

If you multiply -5 X 5 you get -25.


RLJ s-)

 

[macmet]

if you type --> -5^2

into a calculator it will give

--> -25

At least mine does.

 

[ivymike]

The question is then whether -5 means 0-5 or (0-5) or (-5).

 

[TheTick]

Some calculators watch order of operations. Did you use "negative" or "subtract" on your calculator. Could make a difference.

The basis of the argument is whether the negative is applied before the exponent. Consider a negative as subtraction.

PEMDAS:
5^2 = 25
0-25 = -25

 

[macmet]

I used negative....subtract gives me the same result though

 

[swearingen]

RLJ,

Since the power must be evaluated first, it's actually broken down as:

-(5x5) = -25

Try it in MathCAD or Mathematica, or look in a text book. The link I provided shows that the Microsoft programmers understand that it's incorrect as well.

 

[Darken99]

If that is the case they better start a recall on calculators.

 

[swearingen]

Concerning calculators, you have to be careful of how you input the number. If you just input -5 and then try to square it, the calculator is correctly assuming that you want to square the entire thing. It is effectively (-5)^2, which is indeed 25. However, if you can enter a true expression, most calculators will do it correctly. I know that the HP 48 series and TI-86 work just fine...

 

[GBor]

The moral to this story...use lots of parentheses!

 

[IRstuff]

re: Mathcad; Wrong!

-5^2=-25, unless the selection prior to exponentiation is the entire "-5"

TTFN

 

[RLJ]

swearingen, et al,

I do not see -(5X5) as the same mathmatical expression as -5^2.

-(5x5) means that you are simply turning a positive product into a negative value. Mathematically it could also be written as (-1)X(5X5). Mulitplying the -1 through the equation would result in (-5X-5), which would give the answer of "25".

Mathematically -5^2 is the same expression as (-5X-5) and is equal to "25".

Grade school mathematics.


RLJ s-)

 

[GBor]

I think of it as the difference between -(5^2) or (-5)^2. The first is -25; the second is 25. In the order of operations, exponents come before subtraction, so -5^2 would be -(5^2), or -25, but I test every mathematical operation in Excel and MathCad pretty carefully...

 

[swearingen]

RLJ,

So you are saying that when you multiply the -1 through the expression (-1)X(5X5) you get positive 25? You might want to check that one again, and then again with your calculator. Grade school mathematics, eh? A refresher might be in order...

You are, however, exactly right when saying "Mathematically it could also be written as (-1)X(5X5)". Do the parenthesis first, (-1)X(25), then the multiplication and voila, -25, the correct answer.

Try this one:
If, as you say, -5^2 + 5^2 = 50, then do me a favor and bring the -5^2 to the other side using standard algebraic tools. What are you going to do? Add 5^2 to both sides? Go ahead and play with it - you'll see.

 

[handleman]

Swearingen,

You say that M$ "admits" and "understands" that it's incorrect. I read the KB article and saw no such admission. The problem is that the negation operator and subtraction operator both use the same symbol. As long as Excel is consistent about when the (-) symbol is interpreted as negation vs. subtraction I seen no issue. You wasted two hours due to an expression you wrote that, based on numerous posts here, is ambiguous at best. I'm not saying that we shouldn't be aware of this issue. I'm most certainly not saying that M$ always does everything right. But in this case pointing the finger at Excel for your misunderstanding is, in my opinion, unwarranted.

 

[swearingen]

It's actually not ambiguous. By international agreement, mathematicians worldwide have accepted the definition given. As for the KB link, why would it be posted there at all if there was not a problem? Why do they call it "unexpected"? It is because it does not follow the standard order of operations.

To help you, try the exercise I gave RLJ at the end of the post before yours.

I'm not trying to argue, I'm just stating fact. My original post was to help those folks that use the order of operations to understand that Excel uses them incorrectly and to use parentheses in these cases. From the feedback I've gotten on this forum and others, it seems that Excel wasn't the only one to get it confused. In my opinion, it's not worth you guys researching it - just use parentheses. However, if it keeps you up at night, dig out those old textbooks (or sneak one of your children's) and check it out.

Here's a link given by another member of eng-tips:

http://mathforum.org/library/drmath/sets/select/dm_order_op.html

 

[svanels]

Elementary math, order of precedence
Power - square root - multiplication - Division - addittion - subtraction

X² = -25, only when X is an complex number, but that is already beyond basic math.
That is vector stuff.

 

[dford]

Swearingen:

It is good that you warned the forum about the Excel math problem, you probably have had some small (positive) impact on the nations technical productivity. I can imagine your confusion before you resolved Microsoft's error. Quattro, which I use, interprets the math correctly.

Seems that the software community likes to make things difficult on purpose. One that bugs me is the use of
log() instead of the more obvious ln() for natural logarithms in many high level languages.

I have even seen software people write things like
y = x*-1 to negate x. Wierd thing is that the compiler doesn't even blink.

Doug

 

[dfish67]

Funny...How MS is doing it makes sense to me. If there is no term in front of the (-) it is not an operator...it is a sign. Symantics maybe....maybe I'm wierd.

0-5^2=-25

-5^2=25

 

[zekeman]

I have to disagree with the author. It could be taken either way. The naked "-5" to me could assume that a parentheses surrounds the -5 since there is no addition and therefore the implied heirarchy of exponentiation does not exist. On the other hand EXCEL correctly calculates 1-5^2= -24 where addition correctly follows exponentiation.