Excel Math Bug

[Aristotle Polonium]

No. If all were OK, they'd call this program

Excellent

but since it lacks something it's just called

Excel

___________________________________________________________________________

@@ . . . . . . WHISTLE . . . . . . @@

Stick mit triangles, dudenkopf.

Ari

 

[Aristotle Polonium]

BOULDERDASH!!!

Is this a suburb of Boulder, Colorado? If not, it should be.

This is a horrible bug in Excel

Anyone with a modicum of literacy must agree.

Ari
_________________________________________________________

 

[Rainer Rosenthal]

Stick mit triangles, dudenkopf.

| ' . |
| ' ' |
| . ' |
| |
| \ / | PLONK!
| -|- |
| (_) |
| A. P. |
|__________|


--
Rainer Rosenthal, (e-mail address removed) _____________________
| _ | |
| (_) | Given A, P and a circle. Find B, C on the |
| A P | circle with P on BC and area(ABC)=maximum. |
|__________|___(Ingmar Rubin in de.sci.mathematik) ________|

 

[Stephen J. Herschkorn]

I can't until someone gives me the convention to determine whether it
is to be interpreted as y=-(x)^2 or y=(-x)^2



I can't until someone gives me the convention to determine whether it
is to be interpreted as -(x)^2 -18 + 5 = 0 or (-x)^2 -18x + 5 = 0



I don't know until someone gives me the convention to determine
whether it is to be interpreted as -(16)^(1/4)) or ((-16)^(1/4))

I suspect you are being highly disingenous here. Either that, or you
must have had a terrible time getting through high-school level
algebra. The onus is on *you* to provide one reputable source, outside
of computer manuals, where these expressions are not interpreted as I
have been maintaining.

Interesting that you left ouf my example of the Gaussian (or error
function). Have you *ever* seen it written
with extra parentheses as in exp(-(t^2))?

 

[Axel Vogt]

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.

I real enjoy some of the funny answers to that bug
(it is a well known one). What i enjoy most: some
may never looked inside a Math text book.

To keep it running: the following VBA code in Excel
2000 returns -4 as answer:

Sub tst()
MsgBox (-2 ^ 2)
End Sub

Which of course should be wrong if the spraed sheet
answer is right. Or am i wrong? Or is every funny
justification for that MS nonsense to be taken as a
correct argument only if i do not want to apply it
for the coding within Excel? Sorry for being childish.

 

[Harlan Grove]

Interesting and surprising. Thanks for the information.

Purely for the sake of argumentativeness, why surprising? It had to
return -9 or +9, and given the precedence in most programming
languages that provide both unary minus and exponentiation operators
-9 would be more common. Indeed, in VBA -3^2 returns -9.

Conformity with VisiCalc wasn't a good idea. VisiCalc was APL in
reverse - all left to right evaluation. For example, 2*3^4 = (2*3)^4
rather than 2*(3^4), and 2+3*4 = (2+3)*4 rather than 2+(3*4). Just as
in APL, and for the same reason, this is UNAMBIGUOUS.
Counterintuitive, perhaps, but unambiguous. If Excel also enforced
left to right evaluation with no operator precedence and only
parentheses to change evaluation order, then Excel could claim
consistency with VisiCalc, FWLIW. It doesn't, so Microsoft can't claim
conformity with either VisiCalc or 123 to support their choice of
operator precedence. They came up with this all on their own.

That Lotus chose to have 123 behave differently than VisiCalc was a
good thing, IMO. OTOH, Microsoft Excel is aking to COBOL rather than
FORTRAN. How nice!

That said, at least in the context of programming languages, in which
various spreadsheets' formulas are functional languages, there's no
consistent convention for operator precedence. Whether or not this
is/was a mistake is irrelevant to the extent that changing it would be
worse. In any case, this is *not* a bug in Excel, it's a feature
arising from a possibly obtuse design decision.

So far 4 different parsing approaches in spreadsheets and programming
languages have been described: APL's no precedence, strict right to
left; VisiCalc's no precedence, strict left to right; the mainstream's
(123, FORTRAN, BASIC including VBA, Perl, Python, etc.) exponentiation
taking higher precedence than negation; and the fringe's (Excel,
COBOL, apparently AppleScript, REXX and a few others) negation taking
higher precedence than exponentiation.

At the very least, no matter what may be correct in textbooks, there's
ample variety in programming languages, so anyone attempting to use
any programming language to perform calculations on a digital computer
had better check their programming language's operator precedence.
Bitching, whining and moaning about how this is wrong, wrong, terribly
wrong is wasted time, breath and effort.

 

[Harlan Grove]

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.

No kidding! Gee, I'd never have guessed. How odd that this makes C
just like FORTRAN. I've been involved in a few back & forth
discussions with Alan, and I have little doubt he misconstrued C's ^
operator.

FWLIW, the precedence of unary minus/negation and multiplication,
division and remainder operators just doesn't matter. Considering C's
*,

(a * b) == (-a) * (-b) == -(a * (-b))
(-a * b) == (-a) * b == -(a * b)
(a * -b) == a * (-b) [b's - can't be parsed otherwise]
(-a * -b) == (-a) * (-b) == (-a * (-b)) == -(a * (-b))

Unary minus *could* have been given equal to or lower precedence than
*, / and %, and it wouldn't have mattered. I suspect the reason it's
higher is that it was supposed to be given the same as -- and ++, and
those are given higher precedence than *, / and %. Much, much easier
giving all unary operators the same precedence, so they'd be resolved
by the associativity rule alone.

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.

First, what are my conventions? Second, if my favored conventions
exactly matched those of Excel, how would that matter vis-a-vis
negation/unary minus and *, / or %? While the machine may mechanically
parse unary minus earlier than *, / or %, the results would be the
same if it parsed unary minus after.

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

OK, it matters for remainder, but because there's residual ambiguity
about what (-2) % 3 should return. Is the answer -2 or +1? This is
also a matter of convention, and there are differences of opinion.
FWIW, C and Excel differ here, but it's due to the sign convention of
the remainder operator, not to operator precedence. In C, -2 % 3 ==
(-2) % 3 == -(2 % 3) == -2, but in Excel MOD(-2,3) == 1 != or <>
-MOD(2,3) == -2. Note that because Excel uses a function for
remainder, unary minus's operator precedence is immaterial; the signs
of both arguments to the remainder function are necessarily obvious.

 

[Alan Beban]

I suspect you are being highly disingenous here. Either that, or you
must have had a terrible time getting through high-school level
algebra. The onus is on *you* to provide one reputable source, outside
of computer manuals, where these expressions are not interpreted as I
have been maintaining.

Whether I am being highly disingenuous and whether I had difficulty with
high-school level algebra are irrelevant. It seems to me self-evident
(accepting that ^2 is equivalent to a superscript 2) that -x^2, without
an adopted convention as to the order of precedence between negation and
exponentiation, can as readily be interpreted as -(x^2) or (-x)^2; that
is, without an adopted convention, it is ambiguous.

I'm simply asking for a citation to the order of preference convention
that you have indicated is universally accepted (outside of computer
programming). Unless you are claiming that its acceptance is (and
always has been?) so universal that it has never been authoritatively
declared--even in textbooks purporting to be the basis for teaching
unfamiliar students the fundamentals of the language to be used in
high-school algebra. Onus or not, I just find it odd that a thread can
have gone on this long with noone citing a source for a universally
accepted convention, other than assertions about broad usage.

In short, where can one learn about the order of precedence of negation
and exponentiation in mathematics without having to read a slew of
algebra books to measure up common usage.

Alan Beban

 

[Dave Seaman]

Stephen J. Herschkorn wrote:

I'm simply asking for a citation to the order of preference convention
that you have indicated is universally accepted (outside of computer
programming). Unless you are claiming that its acceptance is (and
always has been?) so universal that it has never been authoritatively
declared--even in textbooks purporting to be the basis for teaching
unfamiliar students the fundamentals of the language to be used in
high-school algebra. Onus or not, I just find it odd that a thread can
have gone on this long with noone citing a source for a universally
accepted convention, other than assertions about broad usage.

In short, where can one learn about the order of precedence of negation
and exponentiation in mathematics without having to read a slew of
algebra books to measure up common usage.

You evidently have not read my post in which I cited Lang on the
definition of polynomial rings.

You have seen a wealth of examples demonstrating that -x^2 is universally
understood to mean -(x^2), ranging from the application of the quadratic
formula to the definition of the normal density function, to the formulas
used for differentiation and antidifferentiation of polynomials.

There has been no evidence of a single example in any math book
demonstrating the opposite convention, despite repeated challenges to
produce such an example.

 

[Tom Ogilvy]

VB/VBA/Basic - different product, different rules.

VBA works with Excel as it works with other software packages. There are
many diffences in how they parse and interpret.

To ensure the highest probability of success, one must learn the rules
employed. When advertised behavior doesn't match assumed behavior, then who
is to blame? Personally, when I want to depend on some type of implicit
behavior, I either test it or consult the documentation (and then test it to
see if I understood it correctly).

 

[KeithK]

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

I doubt that any U.S school, including yours, teaches a different convention
than that -z is the negation of z. With z=x^2 you have -x^2 is the negation
of x^2.

Perhaps you were thinking in terms of a plotting sequence:

y = -x
plot(y^2)

which indeed would plot a positive curve, because it is plotting (-x)^2,
which is NOT the same as -x^2.

KeithK

 

[JE McGimpsey]

I doubt that any U.S school, including yours, teaches a different convention
-z is the negation of z.

Of course not. Nobody is disputing that...

With z=x^2 you have -x^2 is the negation of x^2

And of course you know that the second statement doesn't logically
follow from the first. Nor is it logically incorrect. It's simply an
assertion.

Doubt if you must, but the US high school I attended used the opposite
to the supposedly "universal" convention (though it was certainly
discussed as a convention, not "the universal truth", and I'm fluid
enough to accommodate whichever convention(s) are being used).

Since a significant portion of the curriculum for the Calc and DiffEQ
courses (algebra was 7th grade) included daily exercises on the material
using APL (as did my college math courses), that was, perhaps, a natural
consequence. It certainly emphasized the difference between negation and
subtraction, and the *formality* of order of precedence was made more
salient.

Typographically, the negation operator in our textbooks was different
than the subtraction operator, and parentheses were used when needed.

I no longer know what curriculum is used at that school, of course,
though at least one of the faculty members that taught me is still
there. Might be diverting to give her a call...

 

[Peter T]

Stephen J. Herschkorn wrote:

as a follow up to Alan Beban's previous post, snippet:

I suspect you are being highly disingenous here. Either
that, or you must have had a terrible time getting
through high-school level algebra. The onus is on *you*
to provide one reputable source, outside of computer
manuals, where these expressions are not interpreted as I
have been maintaining.

If you'll forgive me, and in the nicest way, you give the
impression of an academic stuck in narrow confinement. You
previously cited convention or notation that is in the
ever on going process of evolution. For readability,
brevity and generally understood it has until recently
served its purpose well. But a convention nonetheless, and
potentially ambiguous.

Alan's point is pertinent and helpful, you (and Dave
Seaman nearby) have missed it. The onus is not on him
to "to provide one reputable source", you may well be
correct that such does not exist. Surely, the point is
that the onus is on the user of a chosen app to explicitly
conform to "its" convention. If you don't like it
don't "chose" to use it. I would be very surprised if
there are any of today's maths students unaware of such
requirement, particularly with something as ubiquitous as
Excel.

My school days were long before the aid of a chip.
Fortunately I was taught to think like this:

+(+1) -(+1) +(-1)

1-1-1 is an abbreviation, partial evaluation or
conventional notation of the above, depending on your
point of view.

BTW, to conform with "another" Excel convention you need
to append the shorter version with an "=" before entering
into a cell. Optionally you may elect to switch to a
Lotus convention. It's one of those need to know things.

Regards,
Peter

PS, in another post Fred wrote along the lines that
negation and subtraction are the same. Maybe you can
correct me but I was taught negation may be considered as

A x (-1)
or, a double subtraction of itself as in
A - A - A

 

[r.e.s.]

in VBA -3^2 returns -9.

I agree with your attitude as stated below, but it sure seems like an
inconsistency when "Cells(1,1)=-3^2" in an Excel macro (VBA) makes the
cell-value -9, while typing "=-3^2" in the cell makes the cell-value 9.
It's the seeming *inconsistency* that's the problem.

At the very least, no matter what may be correct in textbooks, there's
ample variety in programming languages, so anyone attempting to use
any programming language to perform calculations on a digital computer
had better check their programming language's operator precedence.
Bitching, whining and moaning about how this is wrong, wrong, terribly
wrong is wasted time, breath and effort.

--r.e.s.

 

[Dik T. Winter]

What is the value of -16^(1/4)?

The standard is not peculiar to those of us who are bemoaning the bug
here. It is as universal as spelling rules.

Perhaps, but it is spelled out nearly nowhere. Certainly not at school.

The only individuals
claiming this to be an ambiguity appear to be computer professionals
exclusively.

So what?

There are thousands of end users of this product who are
not programmers, nor should one need to be to use a spreadsheet. Nor
should they be expected to comb the documentation to discover this
peculiar "feature." This is a very flawed design.

Would you care to pay a penny for each user that thinks "1 + 2 * 3 = 9"?
I have even seen professional physicians make that error (they enter it
as such on their calculator and get that answer).

Mathematics does not dictate that '*' precedes '+'; that is only
convention. A convention that is (alas) not spelled out in most books.
The Dutch saying for the convention is "Meneer Van Dale Wacht Op Antwoord"
implying the following order:
1. M Machtsverheffen = powering
2. V Vermenigvuldiging = multiplication
3. D Deling = division
4. O Optellen = addition
5. A Aftrekken = subtraction.
Using that rule there are quite a few people that thing that
8 - 3 + 4 = 1
Can you understand why?

 

[Aristotle Polonium]

Very gutsy -- and very original.

Gotcha, dudenkopf. Plonk THIS.

Ari

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Harlan Grove]

I agree with your attitude as stated below, but it sure seems like an
inconsistency when "Cells(1,1)=-3^2" in an Excel macro (VBA) makes the
cell-value -9, while typing "=-3^2" in the cell makes the cell-value 9.
It's the seeming *inconsistency* that's the problem.

Agreed, it's a problem. However, it was unavoidable. BASIC existed long
before any spreadsheet, and BASIC had established operator precedence
(mainstream). Excel existed before VBA, a dialect of BASIC, was grafted onto
it, and the nice people at Microsoft made a bad (but irrevocable in
practical terms) design decision about operator precedence. (Does anyone
know how Multiplan handled -3^2?)

When the two, Excel and VBA, were merged, the inconsistency became truly
exquisite. Excel also uses a sign convention in its financial functions,
something none of the non-Excel clone spreadsheets (so few these days) do or
did. Indeed, there are a lot of questionable design decisions in Excel, but
at this point in time you have to live with them or use something else.

 

[Acid Pooh]

Without a citation to "the definition" you're using, this is not
persuasive.

Your statement begs the question of operator precedence, and using it to
argue for a particular order of precedence is therefore a tautology.

Any introductory course on abstract algebra. -x is the additive
inverse of x: the number such that x + (-x) = 0.

'cid 'ooh

 

[Acid Pooh]

...

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.

Didn't you cover group theory? Exponential notation is quite common
for multiplicative groups, and multiplicative notation is quite common
in additive groups. Example:

In (Z_7, +), x + x defines 2x, x + x + x defines 3x, etc. In (Z_7,
*), x*x defines x^2, x*x*x defines x^3, etc. That's how you're
justified to use things like the least integer property and modular
arithmetic when working with abstract groups.

'cid 'ooh

 

[Vasant Nanavati]

JE McGimpsey <[email protected]> wrote in message

Any introductory course on abstract algebra. -x is the additive
inverse of x: the number such that x + (-x) = 0.

'cid 'ooh

I do believe that most posters in this thread know that. However, while the
meaning of -x may be clear, the meaning of -x^2 is not.