Excel Math Bug

[Jerry W. Lewis]

This is in my help too:

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

But to me it's immaterial. Why don't they follow the normal mathematical
order? If I'm going to sort a list of numbers should I be expected to look
up the sort order to see if they deviated from the norm? No.

Order of operator precedence is a design decision. Design decisions are
made by the developer. If I were writing Excel, I would have
interpreted -5^2 to be -(5)^2 rather than (-5)^2, since you wouldn't
have bothered with the negation if you hadn't wanted it. But the point
remains that neither you nor I wrote Excel; so our choice is to conform
to the developer's design decisions or use a different tool.

As has been repeatedly noted, -5^2 is an ambiguous expression. The
interpretation of ambiguous expressions is based on convention, not
immutable rules of the universe. I will even grant that our preferred
convention is probably the most commonly used convention; but it is not
the only convention, and it does no good to pretend otherwise.

Even if MS regretted this particular design decision, I doubt that they
would change it. Changing it now could break any number of existing
applications, which would cause far more problems and aggravation than
than you are currently experiencing. Once you have finished blowing off
steam, you will have learned something and will not make that mistake
again. Imagine if instead you had to test every single spreadsheet in
your company to see if they still worked, because MS suddenly changed
the rules!

Jerry

 

[JE McGimpsey]

Ask any semi-decent high school student to
draw a graph of y = -x^2, and what will you get?

If you asked someone competent, not just semi-decent, from my high
school, or college, or graduate school, you'd get the same curve as

y = x^2

but it's a convention, not a law, so it wouldn't be surprising to see
the negation of

y = x^2

The flaw is in assuming that you have a lock on absolute truth, rather
than recognizing that when there's ambiguity you need context.

There's no ambiguity that negation and subtraction are different. The
fact that the typography is ambiguous means that you need to check your
assumptions.

Those who insist that a computer application must conform to *their*
standard have never programmed in APL.

 

[Dave Peterson]

In my mind:

Documented = feature.
Undocumented = Bug.

I would still say your problem is with the consultant for not knowing this--or
taking far too long to find it.

I would bet that "by design" would come up in any conversation with MS.

 

[Stephen J. Herschkorn]

In my mind:

Documented = feature.
Undocumented = Bug.

I would still say your problem is with the consultant for not knowing this--or
taking far too long to find it.

I would bet that "by design" would come up in any conversation with MS.

Since when I am supposed to hire a consultant to use a basic spreadsheet
tool?

If you asked someone competent, not just semi-decent, from my high
school, or college, or graduate school, you'd get the same curve as

y = x^2

Then your high school students are very poorly educated. Do they also
consistently misspell the word "friend"?

[...]

The flaw is in assuming that you have a lock on absolute truth, rather
than recognizing that when there's ambiguity you need context.

This is as universal a convention as spelliing rules. I dare you to
find one publication where -5^2 is calculated correctly to be 25.,

Those who insist that a computer application must conform to *their*
standard have never programmed in APL.

This is not just *my* convention.

Software such as spreadsheets, word processors, address books, etc. are
tools provided for the end user, who most likely is not a programmer.
We are not talking JCL here. Your position exhibits the hubris of a
software engineer, whose job is to provide the consumer with a
reasonable product without asking him/her to remember the contradiction
of universally accepted conventions. Hmm, why not make a door which
cannot be opened unless the user stands on his head?

 

[Dik T. Winter]

Do any of you SCI.MATH whizes want to weigh in on this?

Well, contrary to some responders I do not read this in an excel newsgroup
(I have no reason to read such a newsgroup...).

MS Excel calculates "=-5^2" as 25, not as -25.

Yes, that is one of the possibilities.

This is because 'negation' is handled first in Excel. (!?)

Right, there are quite a few programming languages that do the same.

If you put a zero in the equation,
as in "=0-5^2", your answer changes to -25.
Indeed.

Is this in line with standard math rules?

Yup, both are in line with standard math rules. There are no standard
rules about how unary operators are handled.

Is negation different than subtraction?

Yes, indeed.

I've had lots of math and as far as I know
negation and subtraction are the same thing.

When you look at the definition for rings, and stuff like that, you will
find that they are very different. If you look you will find that
a - b
is just shorthand for
a + b'.
where b' is the negative of b. So
0 - 5^2
is shorthand for
0 + (5^2)'

There are more places where some programming languages do not give you
what you thought they should do. Exponentiaton is an example. What is:
a ^ b ^ c?
There is not strict left to right rule in mathematics...

You may wonder, why should I do
a - b + c
from left to right? Well, actually you have to transform it to something
that is basic mathematics:
a + (-b) + c
and now it does not matter what way you do the operations.
where (5^2)' is the negative of 5^2.

 

[Dave Peterson]

The OP hired an excel consultant.

I guess it boils down to a philosophical question.

Do you want to use excel the way it was designed or not?

I guess I don't understand why it took the consultant so long to find the ^h^h^h
his error.

In my mind:

Documented = feature.
Undocumented = Bug.

I would still say your problem is with the consultant for not knowing this--or
taking far too long to find it.

I would bet that "by design" would come up in any conversation with MS.

Since when I am supposed to hire a consultant to use a basic spreadsheet
tool?

If you asked someone competent, not just semi-decent, from my high
school, or college, or graduate school, you'd get the same curve as

y = x^2

Then your high school students are very poorly educated. Do they also
consistently misspell the word "friend"?

[...]

The flaw is in assuming that you have a lock on absolute truth, rather
than recognizing that when there's ambiguity you need context.

This is as universal a convention as spelliing rules. I dare you to
find one publication where -5^2 is calculated correctly to be 25.,

Those who insist that a computer application must conform to *their*
standard have never programmed in APL.

This is not just *my* convention.

Software such as spreadsheets, word processors, address books, etc. are
tools provided for the end user, who most likely is not a programmer.
We are not talking JCL here. Your position exhibits the hubris of a
software engineer, whose job is to provide the consumer with a
reasonable product without asking him/her to remember the contradiction
of universally accepted conventions. Hmm, why not make a door which
cannot be opened unless the user stands on his head?

 

[Stephen J. Herschkorn]

Well, contrary to some responders I do not read this in an excel newsgroup
(I have no reason to read such a newsgroup...).


Yes, that is one of the possibilities.

Can you cite one publication, not a computer langauge manual, where the
author computed -5^2 as 25? We are talking about the convention of the
end user here. If you were helping a child with the school problem to
plot y = -x^2, what you show him/her?

The fact that there are some expressions (e.g., a^b^c) where
precedence is not as standard does not negate that the standard of -x^2
is virtually universal.

 

[Dave Seaman]

If you asked someone competent, not just semi-decent, from my high
school, or college, or graduate school, you'd get the same curve as

y = x^2

but it's a convention, not a law, so it wouldn't be surprising to see
the negation of

y = x^2

The flaw is in assuming that you have a lock on absolute truth, rather
than recognizing that when there's ambiguity you need context.

There's no ambiguity that negation and subtraction are different. The
fact that the typography is ambiguous means that you need to check your
assumptions.

Absolute nonsense. Are you sufficiently competent to apply the quadratic
formula to find the roots of

-x^2 + 8x - 15 = 0

and then verify your results by substitution?

Those who insist that a computer application must conform to *their*
standard have never programmed in APL.

APL is a language unto itself.

 

[fred]

So if a physicist writes an equation he has to include the 'rules of
calculation' for others to use it? Give me a break.

Well, contrary to some responders I do not read this in an excel newsgroup
(I have no reason to read such a newsgroup...).


Yes, that is one of the possibilities.


Right, there are quite a few programming languages that do the same.


Yup, both are in line with standard math rules. There are no standard
rules about how unary operators are handled.


Yes, indeed.


When you look at the definition for rings, and stuff like that, you will
find that they are very different. If you look you will find that
a - b
is just shorthand for
a + b'.
where b' is the negative of b. So
0 - 5^2
is shorthand for
0 + (5^2)'

There are more places where some programming languages do not give you
what you thought they should do. Exponentiaton is an example. What is:
a ^ b ^ c?
There is not strict left to right rule in mathematics...

You may wonder, why should I do
a - b + c
from left to right? Well, actually you have to transform it to something
that is basic mathematics:
a + (-b) + c
and now it does not matter what way you do the operations.
where (5^2)' is the negative of 5^2.

http://www.cwi.nl/~dik/

 

[Myrna Larson]

THIS IS NOT A BUG!!!!!

It's hard to say whether they "believed" you or not. You refused to say what
the issue is/was. If you had described the issue, then no *knowledgeable* user
of Excel would have believed you.

As (I think) JE has pointed out, you can find Excel's order of evaluation of
expressions in Help.

You find the following in the topic "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

Now those may not be the rules you expected, BUT, that's irrelevant.

I expected your "issue" would turn out exactly this way -- not an Excel bug,
just documented behavior that differs from your expecatations.

WRT damages, the EULA says "LIMITED WARRANTY FOR SOFTWARE PRODUCTS ACQUIRED IN
THE US AND CANADA. Microsoft warrants that the SOFTWARE PRODUCT will perform
substantially in accordance with the accompanying materials for a period of
ninety (90) days from the date of receipt."

Operator precedence is documented in those "accompanying materials", so Excel
is performing as documented, and you have no grounds for a suit or for
compensation.

I DO think you may have an "issue" with your consultant. You could easily make
the point that you have paid them to work with a product they aren't qualified
to work with. If your consultant really knew Excel and how to debug formulas,
he/she would have known to write the formula as =-(5^2).

Presumably the consultant DID give you some sort of guarantee, no?

You asked "What other surprizes await?!" If you used Help, you might not find
so many surprises.

 

[Dana DeLouis]

What other surprises await?!

I'm sure there are many. If you are using Mm to help with equations...I
think it has been discussed here before where the consensus is that Excel's
interpreter operates in a kind of "left to right" only mode. So, with a
function like:
=2^3^4
Excel returns 4096 because it's grouping is "left associative." With Mm on
the other hand, Power is "right associative." Therefore:

2^3^4 returns:
2417851639229258349412352

I don't know if there's such a thing as a "Standard" here. One just has to
be aware of them. And in either case, it is better to use () to clearly
indicate meaning as others have mentioned.

( However, if I was a betting man, my money would be on Mm! I think I once
read why this is so, but I can't find it at the moment.) :>)

Dana DeLouis

 

[Tom Ogilvy]

Please site the standard that establishes the normal order for precedence.

As far as sorting, yes you should.

 

[Myrna Larson]

Given the ubiquity of Excel, it seems a bit silly for you to talk about a
"virtually universal" standard, isn't it?

 

[Myrna Larson]

If there is ANY possibility it could be ambiguous, he uses parentheses!

 

[Tom Ogilvy]

the unary negation operator is not the same as subtraction. A basic flaw
in your argument.

Your mnenonic is incomplete. How would you express a negative number in
an equation?

the second flaw is that you attribute the syntax used in writing formulas in
Excel to be synonymous to hand written formulas. But it isn't - it is a
syntax defined by the authors of Excel. Excel defines a set of rules for
its syntax which, to increase comfort, to a large extent parallels hand
written formulas, but nonetheless, it is a syntax defined by the creators of
excel. It is thus incumbent on the user to understand that syntax; not make
assumptions.

 

[fred]

I found a little more on this at:

http://www.macnauchtan.com/pub/precedence.html

It says that many softwares don't comply with 'chalkboard' math rules.

---------------------------------------------------------------------------


Microsoft Excel 4 CAUT[ion] Index
A B C
1 2.0 2 numeric entry
2 = - A1^2 4 return is positive
3 = - A1^2 / 4 1 return is positive
4 = - + A1^2 4 return is +4 without error message

Microsoft acknowledges the disconnect with chalkboard algebra for all
versions of Excel calling it an "unexpected positive value" but argues that
the result is correct because of their order of operations. The suggested
workaround is to use prophylactic parentheses.


----------------------------------------------------------------------------
----
Microsoft Excel 2001 CAUT[ion] Index
A B C
1 2.0 2 numeric entry
2 =-A1^2 4 return is positive
3 =2 2
4 =5 5
5 = - A3^2 / (-A3^2-A4^2) - A4^2 / (-A3^2-A4^2) 1 return is positive
6 =(- A4^2 - A3^2) / (- A3^2 - A4^2) -1 return is negative

The behavior is the same as Excel 4 but note that macros and user defined
functions written in VBA work the opposite way. A user defined function
avoids the unary minus interpretation just as, say, = - SQRT(A1) would.

In the help documentation for Excel 2001 one finds, for the error function
add-in, this definition:



But what is that -t2? The kernel of the integral is exp(-t^2) and the reader
is invited to make a plot of that. You will create a curve,which has value 1
at t = 0 and increases exponentially at each side. It's a far cry from the
infamous Bell Curve that is the correct Gaussian form for the error
function. One needs to call out exp(0-t^2) or exp (-(t^2)) in Excel.

 

[Jerry W. Lewis]

Lance Lamboy wrote:

....

I checked my copy of oocalc. (I don't use M$ products.) Much to my
chagrin it exhibited the same Excel bug.

Historically speaking, I wouldn't put the blame on Microsoft. You can
download a working copy of the original release of VisiCalc from
http://www.bricklin.com/history/vcexecutable.htm
and verify that it worked the same way. I presume that all subsequent
versions of VisiCalc used the same order of operator precedence to avoid
breaking existing applications. I do not have access to a version of
Lotus, but I presume that they utilized the same order of operator
precedence to make it easier for people to switch from VisiCalc to
Lotus. MS probably adopted the same design decision for the same
reason, when they competed with Lotus (just as they preserved the Lotus
mistake of considering 1900 to be a leap year).

The 1900 Leap Year is clearly wrong, but it would have created far more
problems if dates did not import correctly from Lotus spreadsheets, than
to keep existing aps working and warn users that the date system
included a nonexistent date.

However, to call clearly documented operator precedence decisions a
"bug" stretches the definition considerably, since precedence is a just
a convention that attempts to resolve ambiguous expressions. If you are
that concerned about it, then don't write ambiguous expressions -- that
is what parentheses are for.

Jerry

 

[Harlan Grove]

Precedence of operations is stated in the help. As far as I know, there is
no universally accepted order of precedence that covers all combinations and
permutations.

....

Somewhat of a tangent. APL and its descendants (APL2, J and K) have sepaate
tokens for numeric sign and minus operator. The numeric sign character is
part of the number token, so effectively always binds tighter than any other
token or operator. Evaluation is always right to left, so different results
are produced when using the sign character vs the minus operator followed by
a number raised to an even power. Excel treats unary minus like APL's sign
character rather than like APL's unary ('monadic') minus. This is unlike
most other programming languages that use the same character to represent
signs in numeric tokens and unary and 'diadic' minus.

 

[fred]

and some more at:
http://aah.ryan-usa.com/node27.html

It is worth mentioning that some alternative order of operations agreements
actually do give unary operators higher precedence than binary operators.
One example of such an agreement is the one implemented in Microsoft Excel
(http://support.microsoft.com/support/kb/articles/q132/6/86.asp). I happen
to have Excel 2000 at present, and it does in fact evaluate -1^2 to 1
--something I personally would not have believed before preparing this
article.

 

[Harlan Grove]

This is in my help too:

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

But to me it's immaterial. Why don't they follow the normal mathematical
order? If I'm going to sort a list of numbers should I be expected to look
up the sort order to see if they deviated from the norm? No.

You may not like this, but this'll GUARANTEE you get didly squat from
Microsoft. They made NO SECRET of their operator precedence. That you and
your consultant FAILED to read Excel's documentation and simply ASSUMED that
Excel should work the way you expected it to is YOUR OWN FAULT!

Also, this has been discussed time & time again in the Excel newsgroups, so
it ain't new and there's no bounty you can expect for 'discovering' this.

This one's 6 years old.

http://groups.google.com/groups?selm=VA.0000002b.00cef1cc@toshiba-tecra

This one's 9 years old.

http://groups.google.com/[email protected]

Whether you care to believe it or not, your expensive exercise with your
consultant is nothing more than the price of ignorance.