Excel Math Bug

[fred]

with all the input from people like you this has become quite an interesting
thread. It takes me back to the thousands of hours I spent programming for
a bank, and looking up the minute details of certain functions and running
dozens of test routines to check behaviour of functions. It's really quite
amazing how when you push something to its limits you find all kinds of
quirky things.

 

[fred]

Whether you care to believe it or not, your expensive exercise with your

consultant is nothing more than the price of ignorance.

you are right sir. the only mystery to me is why it still exists. After
reading all these posts and searching on my own I now see that "computer
math" can diverge from from "classroom math" on these issues. I consider it
sad but not terribly surprising. I admit to ignorance of the quirks, but
don't admit that it's okay. Why don't many of the softwares work like a
math major would expect? ... Probably because the developers/project
managers weren't math majors. If the program as the subject of discussion
were an English language program, would it be okay for a program to be
making up its own rules of grammer? If the developers and project managers
were not English majors we'd get the same thing as with the math rules.
Would that be okay with you?

Ignorance -- I hope you meant "not knowing", not "being an idiot."

 

[Harlan Grove]

Well, yes, that is basically the same thing a couple of my friends said.
But none of them are mathematicians. The only thing about it is that that's
not how math/algebra/calculus/etc. is done. Negation and subtraction are
one-in-the-same to the best of my somewhat-in-depth knowledge. . . .

Not necessarily. In the very technical sense, 'negation' means taking the
additive inverse of an element of an algebraic ring. In the standard high
level development of algebra, there is no subtract/minus, only adding the
additive inverse of what would be the subtrahend in arithmetic subtraction.
So x - y would always be x + -y. Arithmetic subtraction may be defined in
term of addition and negation, but negation is definitely a more fundamental
concept.

. . . There's even
the mnemonic device "Please Excuse My Dear Aunt Sally" to help remember the
order in which operations are supposed to be executed (Parenthesis,
Exponents, Multipication, Division, Addition, Subtraction).

So? This is a convention, not an immutable universal truth.

To me the only valid argument, based on different priorities, is that
non-math-people, when using actual numbers, not cell references, expected
=-5^2 to be "negative five, squared". And they wanted to satisfy the
secretaries and bean counters, not the heavy-duty math people.

Guess what?! Excel IS NOT MATHEMATICAL SOFTWARE like Maple, Mathematica,
MathCAD, MatLab, etc. It's a SPREADSHEET! It's meant for secretaries and
bean counters, and if others (mis)use it as mathematical software, THEY HAVE
NO ONE BUT THEMSELVES TO BLAIM WHEN IT DOESN'T WORK AS 'EXPECTED'.

However, having done software development myself, I think it most likely
that it's just a bug and just hasn't been fixed yet. In fact, to me,
putting in a zero and getting a different answer proves it's wrong.

No. Putting a zero in front of it converts the unary/monadic negation
operator into a diadic suntraction operator. It's parsed FUNDAMENTALLY
DIFFERENTLY. Even if unary minus had lower priority than exponentiation,
it'd still be parsed differently. Maybe you think you've done software
development, but you don't seem to know much about language design or
parsing.

Given the fun discussions that infrequently occur with regard to different
decimal points and digit groupings in non-English speaking and non-European
countries, are you sure all math texts in all languages adopt the exact same
operator precedence?

 

[Harlan Grove]

Stating it is a documented convention is not a legitimate argument.

....

Rubbish! Ignorance of any program's specs is begging for trouble.

That said, once done, something like this can't be undone. The damage
'fixing' this would cause to those more practical individuals who read
the specs and have designed their spreadsheet accordingly would be far
greater than helping out those who are incapable of addressing their
own ignorance.

As I've written elsewhere in this thread, if you want math software,
use REAL math software. If you use Excel, realize it ISN'T math
software. It's an overgrown calculator. And as a calculator, the unary
minus in Excel is treated NO DIFFERENTLY than the [CHS] button on any
HP RPN calculator or the [+/-] button on any TI calculator. The HP
sequence 3 [CHS] 2 [x^y] returns 9. The TI sequence 3 [+/-] [x^y] 2
[=] returns 9. The Excel formula =-3^2 returns 9. Once you adopt the
correct perspective, this ceases to be a problem.

 

[fred]

Guess what?! Excel IS NOT MATHEMATICAL SOFTWARE like Maple, Mathematica,

MathCAD, MatLab, etc. It's a SPREADSHEET! It's meant for secretaries and
bean counters, and if others (mis)use it as mathematical software, THEY HAVE
NO ONE BUT THEMSELVES TO BLAIM WHEN IT DOESN'T WORK AS 'EXPECTED'.

(Blame, not blaim)
If there isn't "math" to base it on, what is there?

No. Putting a zero in front of it converts the unary/monadic negation
operator into a diadic suntraction operator. It's parsed FUNDAMENTALLY
DIFFERENTLY. Even if unary minus had lower priority than exponentiation,
it'd still be parsed differently. Maybe you think you've done software
development, but you don't seem to know much about language design or
parsing.

"Maybe you think you've done software development" ... okay, whatever,
that's kind of unjustifiably condescending, but
I'm not sure what your point is. I haven't done language design. no.
Why shouldn't a language calculate equations in the way a calculus teacher
would? ... Apparently they just don't. I'm guessing it's because
the programmers don't know the right way to do it,
because most programmers aren't mathematicians;
or because there would be too much work involved at any given point
in the project. Why else?

Given the fun discussions that infrequently occur with regard to different
decimal points and digit groupings in non-English speaking and non-European
countries, are you sure all math texts in all languages adopt the exact same
operator precedence?

I guess that any formula in any physics journal
is univerally calculable. Yes.
If you have to pick a software and then
look up its idiosyncracies instead of assuming it
follows math convention, then I suppose you are right.
Is this right? I say 'no'. Is it common? I guess it is relativly so.

 

[Harlan Grove]

Lance Lamboy wrote:
...

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

And you'd be wrong! In 123, -3^2 returns -9.

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

....

While Lotus is unquestionably to blame for the 1900-as-leap-year bug,
Microsoft actually chose to have Excel behave DIFFERENTLY than 123 in the
beginning. Now in the beginning there was only Excel for the Mac, and 123
only existed for 8086/8-based PCs, so there may have been no perceived need
for Excel to duplicate 123's operator precedence. However, once established
in the Mac world, it would have become a practical impossibility to do
anything different on PCs.

Microsoft gets all the credit/blame for aping VisiCalc rather than 123.

 

[Alan Beban]

BOULDERDASH!!! This is a horrible bug in Excel (whereof I was
previously unaware). It is very standard that exponentiaion takes
precedence over negation. Ask any semi-decent high school student to
draw a graph of y = -x^2, and what will you get?

Stating it is a documented convention is not a legitimate argument.
What if Microsoft(R) buried in its documentation that addition takes
precedence over multiplication? That the spell checker would always
change word "friend" to "freind"? That the sum function adds only every
other term? That using a "q" in one of its products would cause the
system to reboot? These effects would be just as valid by this logic.

I have sent this comment to Microsoft(R), though I expect no good to
come of it.

Pounding on the desk about it being "very standard that exponientation
takes precedence over negation" is much less persuasive than would be
citing the "standard" order of precedence rules applicable in
mathematics. I don't find what "a semi-decent high school student" would
do to be very compelling. Why can't the people who are so emotional
about the issue (which, incidentally, seems to have been resolved in C
the same way as it is in Excel, which is hard to blame Microsoft for)
cite some persuasive authority besides the fact that their grandmother
taught them to Please Excuse My Dear Aunt Sally, which my limited Google
search suggests is applicable to only the binary operators listed and
not to unary operators? Maybe we could all learn something if we were
directed to an authoritative source of the convention in ordinary
mathematics without regard to programming.

Alan Beban

 

[Harlan Grove]

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.

That's the real problem here. The character - is being used both as a sign
character and as an operator. The apparent goal is consistency of results
for, say, -3^2 and -A1^2 when A1 == 3. Excel's results are consistent: both
return 9. FWLIW, Lotus 123's results are also consistent: both return -9.
The consistency of results is what's important, not the precedence.

While this isn't a bug, it was at best a questionable design decision.

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

The only language I know for which this isn't ambiguous because different
characters are used for negative sign on the one hand (part of the number
token) and monadic/diadic minus on the other. PITA to use if you cling to
any preconceived notions of left-to-right evaluation, but NEVER ambiguous.

Actually that's unfair to C and Logo (and probably most other functional
languages). C and Logo don't have exponentiation operators, just power
functions, so again no ambiguity.

 

[Harlan Grove]

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

....

I don't recall exponentiation being covered in the development of either
rings or fields. Just addition and multiplication and their respective
inverses. Exponentiation wasn't brought up until polynomials were
introduced.

Also, you're now arguing for both interpretations. If 5^2 = 25, 0 + (5^2)' =
0 + (25)' = 25' = -25 rather than 25. That's the nub of this whole argument.

 

[Harlan Grove]

Ignorance -- I hope you meant "not knowing", not "being an idiot."

Yes. I admit to my own ignorance from time to time. There's nothing wrong
with ignorance as long as it's addressed once discovered.

Now, elsewhere in this thread I mentioned that Excel really isn't
mathematical software but an overgrown calculator, and Excel's unary minus
operator works like HP [CHS] or TI [+/-] keys when pressed before their
respective [x^y] keys. FWIW, that's the best mnemonic I can offer. However,
if you want to avoid ambiguity at the cost of some loss of flexibility, use
Excel's POWER function instead of the ^ operator. Does the same thing
without ambiguity.

 

[Harlan Grove]

. . . It's really quite amazing how when you push something to its
limits you find all kinds of quirky things.

....

Excel is full of, to be charitable, quirks. See this other thread too.

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

 

[Harlan Grove]

. . . (which, incidentally, seems to have been resolved in C
the same way as it is in Excel, which is hard to blame Microsoft for)

....

What are you talking about?!

Unless you mean to recommend that Excel users stop using the ^ operator and
use the POWER function instead, Excel and C don't work the same. C lacks an
exponentiation operator. It includes a function named pow in its standard
library. It has a ^ operator, but it's bitwise XOR.

 

[Phil Carmody]

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?

Some programming languages don't even treat that as exponentiation.
And not all of those treat it as bitwise XOR.

There is not strict left to right rule in mathematics...

There isn't even strict lexing in mathematics -- is 3 followed by a
raised dot, followed by 14, an approximation to pi, or the answer to
life, the universe and everything?

Convention, convention, and convention.

Phil

 

[Harlan Grove]

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

....

Your argument is akin to saying not everyone drives on the right hand side
of the road in the US if you choose not to. The failure of some individuals
to adhere to standards (something Microsoft has developed quite a reputation
for doing, er, not doing) doesn't alter the existence of standards, only
their enforceability.

However moot since you've deleted sci.math from the newsgroup to which you
sent your response. The person to whom you responded is likely never to see
your response.

 

[Harlan Grove]

Why shouldn't a language calculate equations in the way a calculus teacher
would? ... Apparently they just don't. I'm guessing it's because
the programmers don't know the right way to do it, because most programmers
aren't mathematicians; or because there would be too much work involved at
any given point in the project. Why else?

....

Maybe because computers aren't usually working with equations or true real
(or complex) numbers. Computers generally deal with finite precision binary
arithmetic calculations, and those have complications that have ensnared
many mathematicians who don't appreciate the difference.

For instance, binary floating point addition and multiplication are both
commutative, as expected, but neither is strictly associative, only
approximately so. Evaluation order using the same operator is critical in
software development and usually not at all in mathematics.

 

[Keith Willshaw]

I did in another sub-thread. Dana was familiar with it already.

If you lead off with a negative sign it uses the negative value inside the
exponentiation.
So, instead of =-5^2 equalling -25 it equals 25.
but, =0-5^2 is calculated correctly as -25 even though it's mathematically
the same.

The error is yours

The standard parsing of =-5^2
is -5 * -5

multiplying 2 negative numbers always gives a positive

The acronym to remember is BODMAS

What you should have specified is
=-(5^2)

Keith

 

[Jerry W. Lewis]

And you'd be wrong! In 123, -3^2 returns -9.

Interesting and surprising. Thanks for the information.

Jerry

 

[Phil Carmody]

...

What are you talking about?!

Unless you mean to recommend that Excel users stop using the ^ operator and
use the POWER function instead, Excel and C don't work the same. C lacks an
exponentiation operator. It includes a function named pow in its standard
library. It has a ^ operator, but it's bitwise XOR.

He probably means that unary minus has a significantly higher precedence
than subtraction, so much so that it has a strictly higher precedence
than at least one operator that one would commonly view as being of a
strictly higher precdence than subtraction.

Therefore there is a concrete example of an expression which is
interpretted differently in C than it would be using people like you's
conventions.

In C's case, the precedence inversion is with the multiplicative family
of operators.
e.g. compare C's interpretation of -2%3 as (-2)%3 rather than -(2%3).

Simple, eh? Barely need for an interrobang.

Phil

 

[David Ames]

Amedee Van Gasse wrote:

Programmers are responsible for learning the
languages that they use. It is a bit more problematic when one person
provides the formulas (or even the code) and another implements it, but
that is what testing is for.

Right. You've got to blame the person who was responsible for testing.

I once got burned providing an algorithm that someone else then
implemented in a flavor of Basic that evaluated strictly left to right
with no algebraic hierarchy (other than parentheses) at all. I was
irritated that someone would write a language that worked that way, but
they documented how it worked, so our ignorance of their documentation
was not their fault. Fred's consultant should have known better than to
imply that any developer could be sued for producing a package that
operated as documented.

Fred's consultant immediately blamed Microsoft in order not to be blamed himself.

 

[Dave Seaman]

Pounding on the desk about it being "very standard that exponientation
takes precedence over negation" is much less persuasive than would be
citing the "standard" order of precedence rules applicable in
mathematics. I don't find what "a semi-decent high school student" would
do to be very compelling. Why can't the people who are so emotional
about the issue (which, incidentally, seems to have been resolved in C
the same way as it is in Excel, which is hard to blame Microsoft for)
cite some persuasive authority besides the fact that their grandmother
taught them to Please Excuse My Dear Aunt Sally, which my limited Google
search suggests is applicable to only the binary operators listed and
not to unary operators? Maybe we could all learn something if we were
directed to an authoritative source of the convention in ordinary
mathematics without regard to programming.

By the definition of unary minus, -x^2 is the number such that
x^2 + (-x^2) = 0.

What is the derivative of -x^2 with respect to x? What is the slope of
the tangent line at x = 1? Is it positive or negative?

What is the integral of -x^2 from x = 0 to 1? Is it positive or
negative?