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

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

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.

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

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

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

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

Eng-Tips policies:  FAQ731-376

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

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

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!



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

(OP)
Mathematically speaking, -5^2 = -25 as a consensus worldwide.  The accepted order of operations has you perform the power first.  Remember that a negation (or even a subtraction) is really a positive with a (-1) multipled to it, which is the basis for where it shows up in the standard order of operations.  If you use MathCad or any of the Wolfram products (Mathematica being the one you've probably heard of) this is not an issue because those programs do it correctly.

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

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

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

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

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

So why do various online references list "exponentiation" first, leaving out all the stuff above it in the list (negation, percentage, etc.)?

Hg

Eng-Tips policies:  FAQ731-376

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

isn't this just about how excel interprets what it's asked to do ...

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

To me, -5^2 = 25.  The number to be squared is -5.  The square of -5 is -5 x -5 = 25.  That's also what my TI calculator says.  -(5^2) = -25

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

In addition to Excel, I have another spreadsheet program called Ability Plus.  When I enter =-5^2, the answer is also 25 (not -25).  

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

Most calculators I know of follow the same logic that you say Excel uses with computing “-25^2”.  This issue was covered in one of my high school math classes (in the 1990’s...yes I'm a youngester) when learning how to use a graphing calculator.  

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

MathCAD says X = -5^2 = -25.  I believe this is wrong.  I agree with rb1957 and MichSt.  -5^2 = 25.

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

The (-) sign is treated as an operation that Excel has to do. If you want it to be the sign of the number you would have to put it in (). Maybe that's why MS has done it this way.

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

swearingen:  my version of excel yields 25 for (-5^2)...

Dik

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

Just checked excel and Delphi again and both programs treat the stand alone -5 as a negative 5 and square the number accordingly.  If the expression is part of an equation, then both programs treat it as a subtract sign.  For example 25-5^2 produces a zero as anticipated.  From the link, it appears that the correct evaluation of -5^2 is -25...

thanks, Dik

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

(OP)
A quick note on calculators:

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

My old Quattro Pro evaluates -5^2 as -25 and -5^2+5^2 as 0.

Three other calculators  evaluate this as 25 and 50 without using().

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

In grade school, we were tqaught that squaring a negative number produces a positive number.  Therefore, -5^2 - 25.

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

(OP)
PEinc,

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

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

One side is saying -5^2 is actually -1*5^2, which everybody agrees would be -25.  The other side is saying that -5^2 is actually (-5)^2, or "(negative five) squared."  I've never heard that a negative in front of a number raised to a power is actually -1 times that number raised to a power.  I would tend to think -5^2, with the "naked" negative sign as opposed to a subtraction sign, is 25.

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

I believe that -5^2 = 25, which is what is taught in your basic math classes when learning the order of operations.  However, I’m saying that the way –most- calculators and computer programs operate on -25^2 as (-1)*5^2.  

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

(OP)
You've got a good point that a bunch of engineers can't agree, but there's a difference between agreement and fact.  The fact is that by international standard, the order of operations is as I've presented it.  This is supported by the major mathematics programs, text books, and the Excel programmers themselves.  We can disagree on whether the sky is blue or that -5^2 = -25, but they are what they are...

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

For what its worth I entered it into Mathematica:

-5^2=-25

In my opinion this is what it should be.

Regards,
-Mike

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

If you factor out the -1 from the -5 in -5^2, both the -1 and the +5 should still need to be squared.  Therefore, the -1^2 = 1 and 5^2 = 25 and their product would be +25.  That's how I see it.

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

Here is an algebra web site with an example showing that -3^6 = -729.  Therefore, it follows that -5^2 = -25.  This says I've been wrong.  However, how then can you write the square of -3 unless you use parentheses?

http://www.algebrahelp.com/lessons/simplifying/numberexp/pg2.htm

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

(OP)
PEinc, your question of how can you write the square of -3 without parenthesis is a good one.

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.

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