×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

WARNING! Excel order of operations problem...

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!

RE: WARNING! Excel order of operations problem...

-5^2 is equal to 25!

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

if you type -->  -5^2    

into a calculator it will give

-->  -25

At least mine does.

RE: WARNING! Excel order of operations problem...

The question is then whether -5 means 0-5 or (0-5) or (-5).  

RE: WARNING! Excel order of operations problem...

Some calculators watch order of operations.  Did you use "negative" or "subtract" on your calculator.  Could make a difference.

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

I used negative....subtract gives me the same result though

RE: WARNING! Excel order of operations problem...

(OP)
RLJ,

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

If that is the case they better start a recall on calculators.

RE: WARNING! Excel order of operations problem...

(OP)
Concerning calculators, you have to be careful of how you input the number.  If you just input -5 and then try to square it, the calculator is correctly assuming that you want to square the entire thing.  It is effectively (-5)^2, which is indeed 25.  However, if you can enter a true expression, most calculators will do it correctly.  I know that the HP 48 series and TI-86 work just fine...

RE: WARNING! Excel order of operations problem...

The moral to this story...use lots of parentheses!

RE: WARNING! Excel order of operations problem...

re: Mathcad; Wrong!

-5^2=-25, unless the selection prior to exponentiation is the entire "-5"

TTFN



RE: WARNING! Excel order of operations problem...

swearingen, et al,

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 spinny

RE: WARNING! Excel order of operations problem...

I think of it as the difference between -(5^2) or (-5)^2.  The first is -25; the second is 25.  In the order of operations, exponents come before subtraction, so -5^2 would be -(5^2), or -25, but I test every mathematical operation in Excel and MathCad pretty carefully...

RE: WARNING! Excel order of operations problem...

(OP)
RLJ,

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

Swearingen,

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

(OP)
It's actually not ambiguous.  By international agreement, mathematicians worldwide have accepted the definition given.  As for the KB link, why would it be posted there at all if there was not a problem?  Why do they call it "unexpected"?  It is because it does not follow the standard order of operations.

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://mathforum.org/library/drmath/sets/select/dm_order_op.html

RE: WARNING! Excel order of operations problem...

Elementary math, order of precedence
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...

Swearingen:

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

Funny...How MS is doing it makes sense to me.  If there is no term in front of the (-) it is not an operator...it is a sign.  Symantics maybe....maybe I'm wierd.

0-5^2=-25

-5^2=25

RE: WARNING! Excel order of operations problem...

I have to disagree with the author. It could be taken either way. The naked "-5" to me could assume that a parentheses surrounds the -5 since there is no addition  and therefore the implied heirarchy of exponentiation does not exist. On the other hand EXCEL correctly calculates 1-5^2= -24 where  addition correctly follows exponentiation.

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

(OP)
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.

RE: WARNING! Excel order of operations problem...

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

RE: WARNING! Excel order of operations problem...

(OP)
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.

RE: WARNING! Excel order of operations problem...

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

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

(OP)
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.

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

(OP)
Hear, hear!

RE: WARNING! Excel order of operations problem...

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

Quote:

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

I Agree!

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

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

RE: WARNING! Excel order of operations problem...

what a waste of time!

RE: WARNING! Excel order of operations problem...

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

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

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.

RE: WARNING! Excel order of operations problem...

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

RE: WARNING! Excel order of operations problem...

Amen!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources