MAJOR BUG: Excel operator precedence is wrong

[Guest]

If I enter in a cell "=-1^2" I should see "-1" as the value, not "1". If I
enter "=-A1^2" and the cell A1 contains the value "1", I should see "-1", not
"1". The binary operator "^" should have higher precedence than unary "-",
but does not. (It does have higher precedence than binary "-", as in "=2-2^2"
correctly gives the value "-2", not "0".)

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Guest]

My understanding is that =-1^2 means 9-1 to the power 2) i.e. (-1*-1)=1

=2-2^2 = 2 - (2*2) = 2 -4 = -2

So Excel is correct according to accepted mathematical operator precedence

 

[Bearacade]

Sorry bud, you need to relearn your algebra..

-1^2 is 1

-(1^2) is -1

 

[Bernard Liengme]

The - operators in =-1^2 and in =4-2^2 are not the same.
One is negation (unitary); the other is subtraction (binary)
best wishes

 

[Guest]

Old news. See Help for "The order in which Microsoft Excel performs
operations in formulas."

-1^2 is ambiguous. The most common convention for order of operations would
interpret it as -(1^2), but as documented, Excel will interpret it as (-1)^2,
and will calculate it correctly given that interpretation. There is no
unversal order of operations that applies to all computer software, so the
user must take responsibility for knowing what their particular package will
do if they intend to write ambiguous expressions.

Jerry

 

[Tushar Mehta]

Yes, by most accepted standards, -1^2 should be -1. Unfortunately,
there is no "universally" accepted standard on the priority of unary
negation and exponentiation. MS chose to have XL treat the unary
negation as having a higher priority than exponentiation. And, as of
now, it is hard to imagine MS would revisit the topic.

Of course, this lack of a definite rule leads to inconsistency even
within the XL environment itself since in VBA, -1^2 is -1!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005