Excel 2003 SP2 computes the power of a negative number wrong.

[Guest]

A1 contains -4.
B1 contains the expression -A1^4
The result is supposed to be -256, but instead it is 256.

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

=A1^4 (should give you 256)

 

[Mike Middleton]

Giovanni Ciriani -

See Excel Help "About calculation operators."

Negation is performed before exponentiation.

- Mike
http://www.mikemiddleton.com

 

[David Biddulph]

Hence if you want -256, try =-(A1^4)

 

[Guest]

The negations belongs to the -4. The - in front of A1 should be trated as a
subtraction sign. Exponentiation takes precedence over subtraction.

To prove this point, please enter another formula: =1-A1^4 and for a value
of A1=-4 you obtain -255. Why should the minus in front of A1 be treated with
different precedences depending whether or not there is a 1 in front of it?

Every program that manipulates symbolic math gives precedence to the
exponentiation and then put the minus in front of it. My HP pocket calculator
does that too, and math students are taught the same way in school
(elementary through post-graduate). Previous versions of Excel behaved the
way I was expecting. This versionnis behaving incorrectly and I seriously
believe it to be a misinterpretation of symbolic math.

 

[Arvi Laanemets]

Hi

Rewrite the equation as
=((-1)*(-4))*((-1)*(-4))*((-1)*(-4))*((-1)*(-4))

What do you get as result?

Maybe you wanted
=-(A1^4)


Arvi Laanemets

A1 contains -4.
B1 contains the expression -A1^4
The result is supposed to be -256, but instead it is 256.

----------------
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/community/en-us/default.mspx?mid=e69600e5-fa
db-4745-91bb-eea8435e9c38&dg=microsoft.public.excel.worksheet.functions

 

[Pete_UK]

The UNARY minus operator always takes highest precedence. What would
you say that:

(-4) * (-4) * (-4) * (-4)

should evaluate to?

Hope this helps.

Pete

 

[Tom Ogilvy]

Despite what you believe, Excel has defined the order of precedence for evaluation of its formulas. This has not changed. (also note that negation and subtraction fall in to different areas).

Here is the help from xl2000 for example:

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 — 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. For more information about calculation operators,

Operator Description
: (colon)
(single space)

, (comma)
Reference operators
– Negation (as in –1)
% Percent
^ Exponentiation
* and / Multiplication and division
+ and – Addition and subtraction
& Connects two strings of text (concatenation)
= < > <= >= <> Comparison

 

[Guest]

Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4).

This striking difference goes counter the rule of commutativity additions
established several centuries ago (if not millenia): the order of the number
being added should not change the result of an addition.

 

[Guest]

Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4).

This striking difference goes counter the rule of commutativity additions
established several centuries ago (if not millenia): the order of the number
being added should not change the result of an addition.

 

[Guest]

Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4).

This striking difference goes counter the rule of commutativity additions
established several centuries ago (if not millenia): the order of the number
being added should not change the result of an addition.

 

[Alan Beban]

Then let me ask you a question. Why should -A1^4-1 evaluate to a different
number than 1-A4^4 ? (-255 vs 257 for A1 equal to -4).

This striking difference goes counter the rule of commutativity additions
established several centuries ago (if not millenia): the order of the number
being added should not change the result of an addition.

I think you meant -A1^4+1. And the answer, which you are refusing to
acknowledge even though it's been pointed out to you a couple of times,
is that the minus sign in -A1^4-1 is a negation operator, not a
subtraction operator, so commutativity of subtraction is not relevant.
To see commutativity in action compare =1-(-A1^4)-2 with
=1-2-(-A1^4),where the minus signs preceding the parenthetical and 2
are, in fact, subtraction operators.

Alan Beban

 

[Arvi Laanemets]

Hi

Even when you refuse acknowledge sign and power as separate operators, you
can deduce them only to multiplying operator. So when you have an equation
=-A1^4, then you have a sequence of multiplying class operators, which are
all same weigth and are processed in order from left to right. In formula
=1-A1^4 you have a substraction operator ad an operator of multiplying
class, the later is processed first. It looks like you missed some lessons
in elemantary school math )))

 

[jim sturtz]

actually that is right. any negative number to raised to an 'even' power is
going to be positive.

 

[Guest]

Thks geovanni - quite an experience user....I ll watch on your post...we have
to accept some imperfections....anyway nobody or nothing is perfect....for
sure they got your point (from elementary students and onwards)...

 

[Guest]

Arvi, I do acknowledge that they are separate operators. The expression are
evaluated correctly according to the precedence established by the
programmers. What I'm saying is that the programmer interpreted the specs
regarding negation incorrectly.
The negation should be given priority only if it is in parenthesis, and not
when it is out of parenthesis. That's the only way to keep the rule of
commutativity working.

 

[Guest]

Jim, the trouble is if 1-A1^2 evaluates differently from -A1^2+1 it means
that the rule of commutativity does not hold. To fix that centuries ago it
was established that the - in front of A1 should be applied to the evaluation
of the expression only after the exponetiation. Further to that it was
established that if one wanted to raise to the power a negative number, the
negation should have been enclosed in parenthesis. This way everything works.
Now, somebody wrote specs for the programmers of the spreadsheet
incorrectly, or the programmers interpreted the specs incorrectly. One way or
the other, centuries of a perfectly good algebraic convention should not take
a back seat to a misunderstanding betrween programmer and spec writer.
For this reason many prefer to abund in parenthesis to make sure that the
calculations precedences are not misinterpreted. As several folks, who
replied to my initial post, pointed out if I want a certain result why don't
I put parenthesis around the power. That's OK but it doesn't make the wrong
convention correct.

 

[Guest]

Thanks Mr. G.C.....In our BUILDING Engineering Field, considering the
built-in MATH & trigonometric functions alone, we endlessly use this
Expenential function, and encodes formulas the way mathematics was taught in
the school as based on the CENTURY OLD RULE....thats why, most building
engneers tend to be apart with Excel due to this....Remaining mostly are
those who use excel like a database<search, find, replace> and count 1+1 or
basic mathematical equations. I can feel the students and previous students
to be with you !!! Anyway...Lets just hope that this thread can alarm
Microsoft of their selling value in the succeeding versions....

 

[Guest]

Has anybody tried a symbolic calculator like the HP-19B or the TI-83 and the
rest of the TI family? Let's try a much simpler calculation and then see how
many calculators agree with one result and how many agree with one result or
the other. Let's try -A1^2+A1^2 please post your results:
Excel=8
HP-19B=0
TI-83=0

 

[Guest]

Google Spreadsheets=8
Open Office Calc=8

Has anybody tried a symbolic calculator like the HP-19B or the TI-83 and the
rest of the TI family? Let's try a much simpler calculation and then see how
many calculators agree with one result and how many agree with one result or
the other. Let's try -A1^2+A1^2 please post your results:
Excel=8
HP-19B=0
TI-83=0