WARNING! Excel order of operations problem...
WARNING! Excel order of operations problem...
(OP)
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!
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!






RE: WARNING! Excel order of operations problem...
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.
RE: WARNING! Excel order of operations problem...
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.
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
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.
RE: WARNING! Excel order of operations problem...
also reminds me of a line from Donald Sutherland in "Kelly's Heroes" : Quit givin' me all those negative waves, man!
RE: WARNING! Excel order of operations problem...
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...
RE: WARNING! Excel order of operations problem...
-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
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
These links explain what is going on:
http://support.microsoft.com/kb/q132686/
http://support.microsoft.com/kb/25189/EN-US/
RE: WARNING! Excel order of operations problem...
Hg
Eng-Tips policies: FAQ731-376
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
Dik
RE: WARNING! Excel order of operations problem...
http
RE: WARNING! Excel order of operations problem...
thanks, Dik
RE: WARNING! Excel order of operations problem...
My HP48 also outputs 25 when I square -5. The reason is that in order to enter the -5 you have effectively put parenthesis around it. The calculator assumes that since you entered it first, you wanted it first. If you typed -5^2 + 5^2 and then hit execute, it should give you 0, which is the correct answer. This means that it would be using the order of operations correctly. Excel incorrectly says this is 50.
Again, by international agreement, as stated in dik's link, for -5^2, the number to be squared is 5, THEN the negative is applied. In Tomfh's first link, Microsoft even admits that Excel does not use the standard order of operations (which is the link I was speaking about in my original post):
http://support.microsoft.com/kb/q132686/
Mathematica and MathCAD give the correct output.
RE: WARNING! Excel order of operations problem...
Three other calculators evaluate this as 25 and 50 without using().
RE: WARNING! Excel order of operations problem...
However, the equation 5^2-5^2 = 0 because you doi the powers before the subtraction. There is no subtraction in -5^2 = 25.
RE: WARNING! Excel order of operations problem...
You might want to go back and check your grade school book. Since I tutor grade school through college math on a regular basis, I happen to have a grade school book and they have this exact example in there.
You are exactly correct when you say that squaring a negative number produces a positive number. The problem is that according to the order of operations, you're not squaring a negative number in the example. You're squaring a positive number and then negating the answer.
Why do you think Microsoft actually posted a work-around on their Knowledge Base?
Let me pose it to you this way: Let's assume what you and Excel say is true:
-5^2 + 5^2 = 50
If I want to move the -5^2 to the other side of the equation, how would I go about doing it? If I add 5^2 to both sides, and we stand by your assumption that -5^2 = 5^2, then we'd get:
5^2 + -5^2 + 5^2 = 5^2 + 50
Now if the first two terms cancel, you know it's not right. Now let's try adding -5^2 to both sides:
-5^2 + -5^2 + 5^2 = -5^2 + 50
By your example, the first -5^2 = 25 and the second -5^2 = -25 (negation vs subtraction, as in a previous post) and they cancel and you're left with:
5^2 = -5^2 + 50
Which you'll say can't be true because you contend -5^2 = 25 in your book (in my book, and any math book, it IS true however because -5^2 + 50 = 25).
I don't know what more I can say, but I think we're whipping a dead horse...
RE: WARNING! Excel order of operations problem...
Either way, it is always good practice to use parentheses and/or "-1*..." in these cases to be absolutely sure. Who knows what the programmers had in mind when a bunch of engineers can't even agree on what is correct!
RE: WARNING! Excel order of operations problem...
During my education in using calculators (in high school) and computer software and programs (in college) this point has always been made very clear and we were always warned to use parentheses to be safe. I’m surprised this issue can spark such a long thread…I would think most engineers would be aware of this issue. Maybe this is something that has only been taught in school in more recent times?
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
-5^2=-25
In my opinion this is what it should be.
Regards,
-Mike
RE: WARNING! Excel order of operations problem...
swearingen's equation, 5^2 + -5^2 + 5^2 = 5^2 + 50, could use parentheses, I believe, to make sense.
The real question is whether the number being squared is intended to be -5 or 5. Parentheses would clarify that. A negative number squared is positive.
RE: WARNING! Excel order of operations problem...
http
RE: WARNING! Excel order of operations problem...
http
RE: WARNING! Excel order of operations problem...
If -3^2 = 9, then the issue would be you must use parenthesis to get the other answer: -(3^2) = -9.
It just happens to be the other way around - you don't need parenthesis to get the negative answer and you do need the parenthesis to get the positive answer. This is, I believe, the reason the spreadsheet guys made their change to the standard: most people are looking to get the positive answer, so they were trying to save them the trouble of using parenthesis when, in fact, they actually need them.