warning1 excel order of operations problem

huangyhg

warning! excel order of operations problem...
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!
check out our whitepaper library.
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.
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.
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...
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 subtractionfff"> operator, and the other as the negationfff"> 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.
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!
mathematically speaking, -5^2 = -25 as a consensus worldwide.  the accepted order of operations has you perform the power first.  re  
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
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
thank you for pointing this out swearingeng. i was unaware of it.
these links explain what is going on:
so why do various online references list "exponentiation" first, leaving out all the stuff above it in the list (negation, percentage, etc.)?
hg