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!

 

[electricpete]

There is no bug. If you go to Excel 2003 help and search "precendence" and select the topic
"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.

 

[swearingen]

What puzzles me is that we would never think twice about -x^2. We would square x and then apply the negation. If I had asked you to solve:

-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.

 

[mrMikee]

Here's an example.

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

 

[swearingen]

I completely agree with you, Mike.

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.

 

[mrMikee]

swearington,

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

 

[GBor]

swearingen,

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.

 

[swearingen]

You're close - but there's one small error in your logic. Yes, the math purist would say that the answer would be +/-5. Let's plug that in:

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.

 

[svanels]

Sometimes I long for the days we didn't had computers to dictate what do.
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.

 

[swearingen]

Hear, hear!

 

[GBor]

You must square what's in the parentheses, (-5)^2 = 25.[/quote}

...but if you don't place the parentheses around the -5 when you substitute it back into the original equation:

I Agree!

 

[electricpete]

I have to admit that if I saw -5^2 or -x^2 written on a piece of paper in the context of algebraic equations, I would assume the meaning -(5^2) or -(x^2)based on logic (why would anyone intend to convey (-5)^2 or (-x)^2 when they could simply get rid of the minus.

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.

 

[GBor]

If nothing else, the length of this thread should indicate that there are questions about the convention...

 

[engrom]

what a waste of time!

 

[rob768]

Well, you can't say this topic didn't get any attention

 

[electricpete]

The waste of time is someone who brings a month-old thread to the top of the list soley for the purposes of adding a worthless negative comment.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[prost]

I am with you, electricpete. I may be tired of the subject but that doesn't mean it would be constructive of me to tell add my nickel's worth in the form of a non constructive comment.

 

[engrom]

sorry guyz, if someone thought comment was to offend the thread. And for blames that one month old thread surfaced back with negative comments: the discussion is all about 'negatives'. GBor has rightly given the answer very early in the thread:
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!!!

 

[svanels]

Amen!