WARNING! Excel order of operations problem... 1

[swearingen]

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.

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

 

[JAE]

Hmm..I guess I always understood Excel's order was that they first did the power function and then the * and / functions and then the + and - last.

So -5^2 should be 5 x 5 first....then the negative = -25, which is what you asserted. Perhaps the "naked" negative in front of a number is explicitly attached to the number in Excels twisted logic.

 

[swearingen]

I believe what it was is that most folks, when typing that negative, mean for the whole thing to be negative. I guess Microsoft thought they'd help out by subverting the order of operations.

From a computer programming perspective, I think it's clear that this had to be intentional. Setting the order of operations would hold place in the program no matter where the negative was. In the example I gave, it clearly changes when the problem is reordered.

 

[HgTX]

Never ever trust order of operations. Ever.

Also (in reference to another thread) don't try to use arithmetic operators for boolean operations.

Hg

p.s. If I were using pencil & paper, -5^2 would indeed be 25. But I couldn't see programming that special into Excel...



Eng-Tips policies: faq731-376

 

[Denial]

This is a subtle point, and the advice about using parentheses to make things unabiguously clear is excellent advice.

The subtlety is that the symbol "-" has two possible meanings in mathematics: one as the subtraction operator, and the other as the negation operator (aka the "unary minus"). Excel's help system, in an item titled "About calculation operators", quite clearly states that negation operations are evaluated first, before even percent operations and exponentiation operations.

Unfortunately, it does not explain how Excel determines whether the "-" symbol will be interpreted as denoting subtraction or negation. Commencing a formula with "-" is a dangerous move in any computer language.

Enter =0-5^2 and note the difference.

 

[JAE]

Thus my term "naked" negative sign...nothing in front of it.

also reminds me of a line from Donald Sutherland in "Kelly's Heroes" : Quit givin' me all those negative waves, man!

 

[swearingen]

Mathematically speaking, -5^2 = -25 as a consensus worldwide. The accepted order of operations has you perform the power first. Remember that a negation (or even a subtraction) is really a positive with a (-1) multipled to it, which is the basis for where it shows up in the standard order of operations. If you use MathCad or any of the Wolfram products (Mathematica being the one you've probably heard of) this is not an issue because those programs do it correctly.

That said, the majority of us use Excel because of its ease of use and ubiquitous presence. I guess we'll just have to keep pounding the parenthesis keys...

 

[dik]

A negative number times a negative number should be positive.

-5^2 is 25... as we were taught you might want to include the (-5^2) to prevent confusion... Programming languages, BASIC, C, C++, Delphi, Fortran, etc. treat a negative number squared as being positive.

Dik

Dik

 

[swearingen]

As it turns out, (-5^2) = -25 as well when following the order of operations. I think you're referring to (-5)^2, which does indeed equal 25.

Again, if you check the mathematics sites, you'll find that a negative is treated as multiplying by (-1) which means it falls in line where multiplication would AFTER powers. To illustrate:

-5^2 = (-1)x5^2 = -25
(-5)^2 = ((-1)x5)^2 = 25

 

[Tomfh]

Thank you for pointing this out swearingeng. I was unaware of it.

These links explain what is going on:

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

http://support.microsoft.com/kb/25189/EN-US/

 

[HgTX]

So why do various online references list "exponentiation" first, leaving out all the stuff above it in the list (negation, percentage, etc.)?

Hg

Eng-Tips policies: faq731-376

 

[rb1957]

isn't this just about how excel interprets what it's asked to do ...

i think it'll always return a positive number for a squared operation so -5^2 is treated as (-5)^2, whereas if you want the negative of the square, then you want -1*5^2 ...

i think excel is quite reasonably interpreting -5 as -ve 5

 

[PEinc]

To me, -5^2 = 25. The number to be squared is -5. The square of -5 is -5 x -5 = 25. That's also what my TI calculator says. -(5^2) = -25

 

[PEinc]

In addition to Excel, I have another spreadsheet program called Ability Plus. When I enter =-5^2, the answer is also 25 (not -25).

 

[MichSt]

Most calculators I know of follow the same logic that you say Excel uses with computing “-25^2”. This issue was covered in one of my high school math classes (in the 1990’s...yes I'm a youngester) when learning how to use a graphing calculator.

 

[PEinc]

MathCAD says X = -5^2 = -25. I believe this is wrong. I agree with rb1957 and MichSt. -5^2 = 25.

 

[kxa]

The (-) sign is treated as an operation that Excel has to do. If you want it to be the sign of the number you would have to put it in (). Maybe that's why MS has done it this way.

 

[dik]

swearingen: my version of excel yields 25 for (-5^2)...

Dik

 

[dik]

Boy... crow is tasty... with a little salt and butter... and I see where the error in excel is...

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

 

[dik]

Just checked excel and Delphi again and both programs treat the stand alone -5 as a negative 5 and square the number accordingly. If the expression is part of an equation, then both programs treat it as a subtract sign. For example 25-5^2 produces a zero as anticipated. From the link, it appears that the correct evaluation of -5^2 is -25...

thanks, Dik