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





RE: WARNING! Excel order of operations problem...
-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
RE: WARNING! Excel order of operations problem...
into a calculator it will give
--> -25
At least mine does.
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
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.
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...
-5^2=-25, unless the selection prior to exponentiation is the entire "-5"
TTFN
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
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.
RE: WARNING! Excel order of operations problem...
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.
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
Power - square root - multiplication - Division - addittion - subtraction
X2 = -25, only when X is an complex number, but that is already beyond basic math.
That is vector stuff.
RE: WARNING! Excel order of operations problem...
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
RE: WARNING! Excel order of operations problem...
0-5^2=-25
-5^2=25
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
"The order in which Excel performs operations in formulas" you will see that the unary negation operator -() is evaluated before the binary exponentiation operatoro ()^(). So excel did exactly what is was supposed to.
I would be very surprised if your international convention said that the binary exponentiation operator is supposed to take precendence of the unary negation operator.
I suspect your conclusions regarding precendence by that standard may be incorrect and may arise from an incorrect assumption that the unary negation operator -() has the same precendence as the binary subtraction operator () - (). In general it does not. (in general unaries have higher precedence than binaries).
Regardless of whether there is any one true correct way, I agree with have to agree with handleman that the burden is on the user to write unambiguous equations and/or test your application to see what it does. I would not assume that all software complies to some international agreement.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: WARNING! Excel order of operations problem...
-x^2 + 5 = -20
what answer would you have given me? You would have subtracted 5 from both sides, divided both sides by -1 and then taken the square root. The answer is x = 5. Now plug 5 back into the equation. Are you telling me you get something different all of a sudden? The only way this works is if -5^2 = -25.
I fail to see how:
-x^2 + x^2 = 50
Can't happen.
How many times have you rearranged equations thus:
-x^2 + x^2 = x^2 - x^2
Are you telling me this isn't true? If you say it's true and I told you x was 5, would you hesitate and tell me suddenly it was false?
We're far more used to seeing it in algebraic abstract form. Usually, we never need to write "-5^2", we're doing it in the abstract. I think that's where the confusion comes in. Now, if you do what I did in my spreadsheet and put a cell number in for that x, you can see where it got screwed up.
RE: WARNING! Excel order of operations problem...
Assume we have a right triangle with a hypotenuse of 6.25 and one side that is 5. If the unknown side is 'x' we can write the following equation.
6.25^2 = 5^2 + x^2
This can be rewritten as
x^2 = 6.25^2 - 5^2
= 6.25*6.25 - 5*5
= 39.0625 - 25
= 14.0625
x = 3.75 ...which is actually part of a 3,4,5 triangle.
Regards,
-Mike
RE: WARNING! Excel order of operations problem...
What the others are saying is that you can't rearrange the equation thus:
x^2 = -5^2 + 6.25^2
I say you can and nothing changes. It just makes sense.
RE: WARNING! Excel order of operations problem...
The way you rearranged the equation should be the same as what I did.
If Excel does it differently that's up to Microsoft. I don't use Excel.
-Mike
RE: WARNING! Excel order of operations problem...
I'm right with you, but a math purist will tell you in your previous argument that when you take the square root of 25, you get +5 and -5. Not sure that it really effects your argument, but if you don't place the parentheses around the -5 when you substitute it back into the original equation: -(-5)^2 + 5 = -20 you would have --5^2+5=-20. Because I don't always think order of operations, I would be inclined to negate the double negative first and come up with 5^2 + 5 = -20, which, of course, isn't correct...that's why I use a lot of parenthetical expressions in Excel.
RE: WARNING! Excel order of operations problem...
For the +5:
-(+5)^2 + 5 = -20
-(5)^2 + 5 = -20
-25 + 5 = -20
-20 = -20
For the -5:
-(-5)^2 + 5 = -20
You must square what's in the parentheses, (-5)^2 = 25.
-(25) + 5 = -20
-25 + 5 = -20
-20 = -20
There isn't a double negative if you strictly follow the proper order. The point is both answers work in the example.
RE: WARNING! Excel order of operations problem...
Now we are arguing about how excel (the new computer god) interprets a sign, and the cheops pyramide was built without Micro$oft, and Mr. Eiffel was using brain, pencil and paper to build something that still stands out as a sign of human intelligence.
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
I Agree!
RE: WARNING! Excel order of operations problem...
But we don't want our computers making assumptions about what we mean. We want them following consistent rules. The rules are stated in excel help. There is no universal standard or anything close to it that I know of.
-5^2 will equal 25 in
mysql
quickbase
java
excel
-5^2 will equal -25 in
matlab
scilab
maple
So now I have to agree with all the original poster's comments that many users might expect the opposite results (-5^2=-25) from their experience in algebra and some but not all other computer environments. Also I think everyone agrees with his suggestion about using parantheses. I just don't think we should bash microsoft for picking a convention and sticking to it (there are enough legitimate reasons to bash them)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
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...
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: WARNING! Excel order of operations problem...
RE: WARNING! Excel order of operations problem...
GBor (Mechanical) 13 Jun 06 17:38
The moral to this story...use lots of parentheses!
Why blame microsoft & all written softwares & macros? Blame should go to diminished thinking capabilities of human brain by overly relying on softwares!!!
RE: WARNING! Excel order of operations problem...