Incorrect order of operations in formula
Author Message Incorrect order of operations in formula

I just noticed that Excel does not use correct order of operations
with - and ^.  For example, the formula = - 5 ^ 2 incorrectly displays
25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2, when
it should be interpreted as -(x^2).

This is also a problem with multiplication; 1*-5^2 also yields 25.
On the other hand, addition is fine; if it wasn't I'd be really worried.
So 0-5^2 yields -25.

If anyone can tell me why this makes sense in somebody's world,
I'd like to know.

Also, if the problem needs to be reported, how do I do it?

Thanks,

Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
Jonathan,

That is indeed the correct order of operation. Negation is the highest
(first) priority of standard arithmetic operations. Subtraction, which
uses the same symbol as negation, is lower priority than negation or
exponentiation, but is a completely different operation.

The two formulas
=-5^2
and
=0-5^2
are NOT the same.  In the first, the - is a negation operator.  In the
second, it is a subtraction operator.

This is the way programming languages work.  It is not a problem, and
does not need to be reported.

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel

Quote:
>I just noticed that Excel does not use correct order of operations
>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly
displays
>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2,
when
>it should be interpreted as -(x^2).

>This is also a problem with multiplication; 1*-5^2 also yields 25.
>On the other hand, addition is fine; if it wasn't I'd be really
worried.
>So 0-5^2 yields -25.

>If anyone can tell me why this makes sense in somebody's world,
>I'd like to know.

>Also, if the problem needs to be reported, how do I do it?

>Thanks,

>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
This issue was raised in the Excel newsgroups about a year or so ago,
and I'm still waiting for someone to cite me their persuasive authority
for what they deem to be a "correct" order, or that an expression
"should be" interpreted a particular way.  Many programmers
systematically avoid any problem by routinely using appropriate
parentheses to force the intended meaning.  My own notion of what's
going on is that there is a fairly well accepted notion of a negation
operator, -, that is different from the subtraction operator, with a
different order of precedence in most programming languages, and people
try to assert that it should be assigned the same order of precedence as
the subtraction operator--but I've never seen a decent discussion of
this.

It seemed at the time to be an issue that people got emotional over, but
nobody wanted to rationally discuss it.  People even expressed the view
that it was an arrogant Microsoft innovation to "reverse" the "basic
rules".  I'm curious why the poster here thinks that there is some
natural or unnatural law about the order of precedence in this case.

Let's get a good, hot (but civil, of course) thread going.

Quote:

> I just noticed that Excel does not use correct order of operations
> with - and ^.  For example, the formula = - 5 ^ 2 incorrectly displays
> 25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2, when
> it should be interpreted as -(x^2).

> This is also a problem with multiplication; 1*-5^2 also yields 25.
> On the other hand, addition is fine; if it wasn't I'd be really worried.
> So 0-5^2 yields -25.

> If anyone can tell me why this makes sense in somebody's world,
> I'd like to know.

> Also, if the problem needs to be reported, how do I do it?

> Thanks,

> Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
Jonathan,
You are comparing negation and subtraction as equals, but they are not:

You might want to consult the help before reporting this:

The order in which Microsoft Excel performs operations in formulas

If you combine several operators in a single formula, Microsoft Excel
performs the operations in the order shown in the following table. If a
formula contains operators with the same precedence ? for example, if a
formula contains both a multiplication and division operator ? Microsoft
Excel evaluates the operators from left to right. To change the order of
evaluation, enclose the part of the formula to be calculated first in
parentheses.

Operator Description
: (colon)  Reference Operators
, (comma)
(single space)
Negation (as in 1)
% Percent
^ Exponentiation
* and / Multiplication and division
& Connects two strings of text (concatenation)
=  <  >  <=  >=  <> Comparison

Regards,
Tom Ogilvy

Quote:
>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
Hi Jonathan,

Excel is using the order as shown in help under "calculating formulas,
calculation order".  Negation is figured before Exponentiation.

I always use parentheses whether they're needed or not.  For example I'd use
=(2*5)+1 even though =2*5+1 would give the same result.  I find it easier to
read and less prone to errors.  In your case you can force the order you
want with = -(5^2).

I'll leave it for others to argue whether Excel's order of calculation is
the "correct" order.

HTH
Denny Campbell
Grand Rapids, MI  USA

Quote:
>I just noticed that Excel does not use correct order of operations
>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly displays
>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2, when
>it should be interpreted as -(x^2).

>This is also a problem with multiplication; 1*-5^2 also yields 25.
>On the other hand, addition is fine; if it wasn't I'd be really worried.
>So 0-5^2 yields -25.

>If anyone can tell me why this makes sense in somebody's world,
>I'd like to know.

>Also, if the problem needs to be reported, how do I do it?

>Thanks,

>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
I realized after writing that my comparison with subtraction was
flawed, and that the unary minus is a different operator than the
binary minus.  So, I didn't help by confusing the issue.

but...

FORTRAN seems to follow the convention I considered "correct;"
a=-5**2
print*,a
yields -25.00000

C isn't a good reference since the power operator is a function

The IDL (Integrated Data Language) yields -25 for
print,-5^2.

So, Excel is the only "language" I regularly use that
acts other than the way I see as intuitive.  I would be
interested in knowing why just for my edification.

My reasons for thinking that it makes more sense to
perform ^ before - is that a) that would be the
normally understood order if I were writing formulas
by hand, and b) it is more compact.

I apologize for reopening this debate and for
confusing the issue; I guess I didn't search for the
right words on dejanews when I was looking to see
if it had been discussed before.

Thanks,

Jonathan

Quote:

>Jonathan,

>That is indeed the correct order of operation. Negation is the highest
>(first) priority of standard arithmetic operations. Subtraction, which
>uses the same symbol as negation, is lower priority than negation or
>exponentiation, but is a completely different operation.

>The two formulas
>=-5^2
>and
>=0-5^2
>are NOT the same.  In the first, the - is a negation operator.  In the
>second, it is a subtraction operator.

>This is the way programming languages work.  It is not a problem, and
>does not need to be reported.

>Cordially,
>Chip Pearson
>http://home.gvi.net/~cpearson/excel

>>I just noticed that Excel does not use correct order of operations
>>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly
>displays
>>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2,
>when
>>it should be interpreted as -(x^2).

>>This is also a problem with multiplication; 1*-5^2 also yields 25.
>>On the other hand, addition is fine; if it wasn't I'd be really
>worried.
>>So 0-5^2 yields -25.

>>If anyone can tell me why this makes sense in somebody's world,
>>I'd like to know.

>>Also, if the problem needs to be reported, how do I do it?

>>Thanks,

>>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
Actually, Jonathan, I sorta enjoyed it. As has been pointed out before in this
group (I think it was this one, and I think Chip did the pointing), once you add
VB or VBA to your set of languages you will get the behavior you expect: -5^2 =>
-25, as you also get with MatLab, Mathematica and S-Plus. Excel is the only
language I know of that gives higher precedence to the unary "-" than to the
power operator.  But along the lines of what Alan suggested, I use parens
liberally just for my own head's sake (it needs a lot of help).

Regards to all,
TMY Research

Quote:

> I realized after writing that my comparison with subtraction was
> flawed, and that the unary minus is a different operator than the
> binary minus.  So, I didn't help by confusing the issue.

> but...

> FORTRAN seems to follow the convention I considered "correct;"
> a=-5**2
> print*,a
> yields -25.00000

> C isn't a good reference since the power operator is a function

> The IDL (Integrated Data Language) yields -25 for
> print,-5^2.

> So, Excel is the only "language" I regularly use that
> acts other than the way I see as intuitive.  I would be
> interested in knowing why just for my edification.

> My reasons for thinking that it makes more sense to
> perform ^ before - is that a) that would be the
> normally understood order if I were writing formulas
> by hand, and b) it is more compact.

> I apologize for reopening this debate and for
> confusing the issue; I guess I didn't search for the
> right words on dejanews when I was looking to see
> if it had been discussed before.

> Thanks,

> Jonathan

> >Jonathan,

> >That is indeed the correct order of operation. Negation is the highest
> >(first) priority of standard arithmetic operations. Subtraction, which
> >uses the same symbol as negation, is lower priority than negation or
> >exponentiation, but is a completely different operation.

> >The two formulas
> >=-5^2
> >and
> >=0-5^2
> >are NOT the same.  In the first, the - is a negation operator.  In the
> >second, it is a subtraction operator.

> >This is the way programming languages work.  It is not a problem, and
> >does not need to be reported.

> >Cordially,
> >Chip Pearson
> >http://home.gvi.net/~cpearson/excel

> >>I just noticed that Excel does not use correct order of operations
> >>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly
> >displays
> >>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2,
> >when
> >>it should be interpreted as -(x^2).

> >>This is also a problem with multiplication; 1*-5^2 also yields 25.
> >>On the other hand, addition is fine; if it wasn't I'd be really
> >worried.
> >>So 0-5^2 yields -25.

> >>If anyone can tell me why this makes sense in somebody's world,
> >>I'd like to know.

> >>Also, if the problem needs to be reported, how do I do it?

> >>Thanks,

> >>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
Actually, Jonathan, I sorta enjoyed it. As has been pointed out before in this
group (I think it was this one, and I think Chip did the pointing), once you add
VB or VBA to your set of languages you will get the behavior you expect: -5^2 =>
-25, as you also get with MatLab, Mathematica and S-Plus. Excel is the only
language I know of that gives higher precedence to the unary "-" than to the
power operator.  But along the lines of what Alan suggested, I use parens
liberally just for my own head's sake (it needs a lot of help).

Regards to all,
TMY Research

Quote:

> I realized after writing that my comparison with subtraction was
> flawed, and that the unary minus is a different operator than the
> binary minus.  So, I didn't help by confusing the issue.

> but...

> FORTRAN seems to follow the convention I considered "correct;"
> a=-5**2
> print*,a
> yields -25.00000

> C isn't a good reference since the power operator is a function

> The IDL (Integrated Data Language) yields -25 for
> print,-5^2.

> So, Excel is the only "language" I regularly use that
> acts other than the way I see as intuitive.  I would be
> interested in knowing why just for my edification.

> My reasons for thinking that it makes more sense to
> perform ^ before - is that a) that would be the
> normally understood order if I were writing formulas
> by hand, and b) it is more compact.

> I apologize for reopening this debate and for
> confusing the issue; I guess I didn't search for the
> right words on dejanews when I was looking to see
> if it had been discussed before.

> Thanks,

> Jonathan

> >Jonathan,

> >That is indeed the correct order of operation. Negation is the highest
> >(first) priority of standard arithmetic operations. Subtraction, which
> >uses the same symbol as negation, is lower priority than negation or
> >exponentiation, but is a completely different operation.

> >The two formulas
> >=-5^2
> >and
> >=0-5^2
> >are NOT the same.  In the first, the - is a negation operator.  In the
> >second, it is a subtraction operator.

> >This is the way programming languages work.  It is not a problem, and
> >does not need to be reported.

> >Cordially,
> >Chip Pearson
> >http://home.gvi.net/~cpearson/excel

> >>I just noticed that Excel does not use correct order of operations
> >>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly
> >displays
> >>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2,
> >when
> >>it should be interpreted as -(x^2).

> >>This is also a problem with multiplication; 1*-5^2 also yields 25.
> >>On the other hand, addition is fine; if it wasn't I'd be really
> >worried.
> >>So 0-5^2 yields -25.

> >>If anyone can tell me why this makes sense in somebody's world,
> >>I'd like to know.

> >>Also, if the problem needs to be reported, how do I do it?

> >>Thanks,

> >>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT  Incorrect order of operations in formula
This isssue is discussed at length in the comp.apps.spreadsheets FAQ, which
is maintained by Russell Schulz and is available at:

----- Posted by John Walkenbach of JWalk & Associates -----
-----          Visit "The Spreadsheet Page"           -----
-----            http://www.j-walk.com/ss             -----

Quote:
>I just noticed that Excel does not use correct order of operations
>with - and ^.  For example, the formula = - 5 ^ 2 incorrectly displays
>25 instead of -25.  In other words, -x^2 is interpreted as (-x)^2, when
>it should be interpreted as -(x^2).

>This is also a problem with multiplication; 1*-5^2 also yields 25.
>On the other hand, addition is fine; if it wasn't I'd be really worried.
>So 0-5^2 yields -25.

>If anyone can tell me why this makes sense in somebody's world,
>I'd like to know.

>Also, if the problem needs to be reported, how do I do it?

>Thanks,

>Jonathan Bishop

Wed, 18 Jun 1902 08:00:00 GMT

 Page 1 of 1 [ 9 post ]

Relevant Pages