hierarchy problem with minus sign in front of parentheses =-(1)^2

  • Thread starter Thread starter Brian Murphy
  • Start date Start date
B

Brian Murphy

=-(1)^2

The above produces 1 in excel 95 through excel 2002.

I think it should produce -1.

In the VBA immediate window Msgbox -(1)^2 produces -1, as expected.

Is this behavior documented somewhere?

Thanks,

Brian Murphy
Austin, Texas
 
You can find the rationale for this behavior in the Excel and Excel VBA help
by performing a search for order of operation.

In Excel help, it notes that negation occurs prior to exponentiation which
occurs prior to addion and subtraction. So the worksheet first makes the 1
negative then squares it, resulting in 1 (-1 x -1)

In Excel VBA help, exponentaion comes first, then negation. So VBA squares
1 (resulting in 1) and then makes it negative resuting in -1.

Rather inconsistent and I'm sure somewhat frustrating. Hey Bill! Are you
listening???

HTH
 
Hi Brian,

I agree its a curious choice (Lotus compatibility?), but it is documented in
Excel help: search for
Operation Order
and you will see that negation is performed before exponentiation.

In visual Basic search help for Operator Precedence and you will see that
exponentiation comes before negation.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
(Lotus compatibility?)

1-2-3 2.2 returns -1, so it doesn't look as if that's the reason.
 
As with many order of operation problems, it can be readily controlled
without ambiguity:

=-((1)^2)

Alan Beban
 
Thanks to all for the replies.

Now that I see that the hierarchy is spelled out in the online help, and the
program is doing what it says it should, I feel a bit better about it.

Without knowing why Microsoft decided to use different hierarchy schemes in
wks and vba, I'm inclined to say that the choice was not a wise one.

One of the longest legacies in computation is the way the FORTRAN language
does hierarchy, which I'm fairly certain matches the way VBA does it. So
why be different on the worksheet???

Brian
 
Back
Top