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

[Harlan Grove]

Giovanni Ciriani wrote...

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.

....

First, commutivity has NOTHING to do with this. Nothing! In ALL
programming languages and all expression-driven numerical computation
software I've seen, unary minus ALWAYS has higher precedence than the
dyadic subtraction operator. -a - b = -(b - -a) (dyadic - is
anticommutative) and -a + b = b + -a (dyadic + is commutative). If you
add other operators, you need to parse the expressions correctly, and
you don't seem to be doing that.

Second, Excel is NOT unique among programming languages or
expression-driven numerical computation software in giving unary minus
higher operator precedence than exponentiation. That *IS* contrary to
the standard bodmas (the English acronym) math/science textbook
operator precedence, but Excel is highly idiosyncratic about which
standards it follows. FWLIW, Excel's operator precedence is the same as
COBOL and some SQL dialects. It has also been in place for decades now,
so changing it merely to suit purists' whims could break many existing
spreadsheet models.

Getting back to the similarity to COBOL, if Excel's original
programmers believed that most Excel users back in the mid 1980s would
have been more familiar with COBOL than math texts, their choice of
operator precedence would NOT have been a mistake, just an unfortunate
design decision. However, changing that operator precedence now would
be a MONUMENTAL MISTAKE for the reason already given: it'd screw up
millions if not billions of existing spreadsheets.

Besides, this has been discussed many times in the past. There's a much
longer thread in the comp.sci.math ng archives that goes into this in
much greater depth. Yes, Excel's operator precedence is unfortunate.
No, it's not unique. No, it probably wasn't a mistake. No, it's not
going to change in our lifetimes. So . . . get used to it.

 

[Harlan Grove]

Giovanni Ciriani wrote...

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

....

Your error here comes from confusing the unary minus operator with the
dyadic subtraction operator. In bodmas texts, unary minus is treated
implicitly as multiplication by -1, so -x^4 could be expanded as
(-1)*x^4, and bodmas would evaluate this as (-1)*(x^4). This is just a
convention. Unfortunately, Excel adopts a different but internally
self-consistent convention of treating -x implicitly as (0-x), and the
parentheses are *INTENDED*.

Also, your first expression has 3 operators while your second has only
2. It's not immediately obvious why changing the number of operators
shouldn't change the values. Perhaps you should have written the second
as 1--x^4, in which case -x^4-1 = 255 and 1--x^4 = -255, which is
EXPECTED since subtraction is anticommutative: a - b = -(b - a). Or you
could have written the first as -x^4+-1 and the second as -1+-x^4, and
in that case both would equal 255. Commutivity and anticommutivity are
preserved as long as you use APPROPRIATE arithmetic rephrasing, and
that REQUIRES distinguishing between - as unary minus and as
subtraction. IOW, Excel's convention, PROPERLY UNDERSTOOD, is
self-consistent. Your mistake is assuming a--b^c = a+b^c as it would in
bodmas.

In some programming languages there's a different token for the
arithmetic sign of negative numeric literals. APL and its offspring are
prime examples. In those languages the sign token is part of the
numeric literal, so if I were to use underscore, _, as such a token,
0-3 = _3, and _3^4 would always be interpretted as (0-3)^4. This
probably wasn't Excel's original developers' explicit rationale, but I
suspect they believed that users would expect -3^2 to be evaluated as
(-3)^2 when the base was a negative numeric literal, so they chose to
have unary minus behave the same way: -x^2 = (-x)^2. Their convention
is equivalent to translating all instances of -x (unary -) as (0-x).

Mathematicians back in the 17th century were lazy in this regard, and
used the same symbol to represent numeric sign, sign change and
subtraction. In order to resolve the ambiguity that this produces,
mathematicians and other writers who use mathematics adopted the bodmas
convention. Excel adopted a different convention. Unfortunate, but as
likely to be resolved as the UK, Ireland, Japan, Australia and New
Zeeland are to switching to driving on the right side of the road.

 

[Harlan Grove]

Giovanni Ciriani wrote...

Google Spreadsheets=8
Open Office Calc=8

....

Because both adopted Excel's operator precedence.

This has been done before, but WTH.

Gnumeric 1.6.x 8 though it changes the formula to =(-A1)^2+A1^2
Lotus 123 R97 0 Lotus always understood math better than
Microsoft
VBA 0 using ? -2^2+2^2 in the Immediate window

In short, any software that puts compatibility with Excel worksheet
formula syntax as a high priority is likely to produce the same results
as Excel, and other software (aside from COBOL apps and some SQL
dialects) won't.

 

[Guest]

Thats enough Geovanni....Lets find solution (at least temporary) so that the
faithful user like me can still continue with all the workbooks established
by engineers like me....I will start a new ? thread to find solution by
find/replace functions, hopefully to be replied by other excel
users....thanks anyway....see THREAD : EXCEL FUNCTION CROSS SOLUTION ?

 

[Guest]

Unfortunate, but as
likely to be resolved as the UK, Ireland, Japan, Australia and New
Zeeland are to switching to driving on the right side of the road.

Any citizen of those countries would argue that they DO drive on the right
side of the road...

 

[Harlan Grove]

Ryan Poth wrote...

Any citizen of those countries would argue that they DO drive on the right
side of the road...

Right meaning opposite of left. However, I'll accept your implicit
argument that they can't tell right from left.

 

[Roger Govier]

Ouch!!!!
Right as in Correct<vbg>