Excel Math Bug

[JE McGimpsey]

Now that _is_ a problem. I don't know whether to laugh or cry.

I store all my tables in a RDBMS, so I'll laugh.

...

Laugh all you want, Postgresql shares this operator precedence.

http://www.postgresql.org/docs/7.2/interactive/sql-precedence.html
[/QUOTE]

As does mysql

http://dev.mysql.com/doc/mysql/en/Operator_Precedence.html

IIRC, Oracle doesn't have an exponentiation operator (having the
POWER(m,n) function instead), but negation has a higher precedence than
multiplication or division.

 

[Bruce Ikenaga]

...

I doubt any math as opposed to programming text uses carets, ^, to
denote exponentiation. Typographically this particular ambiguity is
resolved by showing the minus sign to the left of the horizontal line
used to separate the x^2 numerator from the 2 denominator. No one
disagrees that exponentiation should precede division, and it's clear
that negation applies to the result of the division. Things would be
less clear if the minus sign immediately preceded the x in the
numerator term.

Of course. The carets are there for the sake of a USENET post. And
things are exactly as you described in your last sentence in example 6,
page 278, of the Larson/Edwards text I referred to earlier.

me that -3^2 is -9. So does maxima. So does gap. So does my TI

calculator.

As for your TI calculator, either you're pressing the key sequence

3 [+/-] [x^y] 2 [=] which returns +9

or

3 [x^y] 2 [=] [+/-] which returns -9

In either case you're applying an unambiguous and manual operator
precedence. If you turn on a TI calculator or clear the register so it
shows 0, then press [+/-], the register still displays and contains 0
(zero), not -0, and when you then key in 3, it displays and contains
3. There's no way to represent leading minus signs with a TI
calculator. Your arguments would be more persuasive if it weren't so
obvious you were dreaming some examples up on the fly.

Your last sentence is ironic considering that you jumped to an
incorrect conclusion concerning the kind of calculator I was
using. But I accept responsibility for not giving the model
number. And no, I'm not into making up stuff to win arguments,
if that's what you mean.

All the TI calculators we support are graphical algebraic entry
calculators, and the one I had at hand (on which I tried the
example I gave) was a TI-82. You're describing the keypresses
for an old-fashioned "scientific" one-line display calculator, I
guess. On my 82, I press

(-) 3 ^ 2 <enter> and get -9

or

(-) 3 <x^2> <enter> and get -9

Here the (-) key is the unary minus key, <x^2> is the squaring key
which produces a power (superscript) 2, and <enter> is the usual
<enter> key. The first sequence produces the display "-3^2" before
<enter> is pressed; in the second case, the "^2" is replaced by
a "real" power of 2.

If you'd like to confirm this for yourself, go to Office Max or
Staples or some place which carries the TI 82/83/84/85/86 and try
it out for yourself.

Bruce I.

 

[Bruce Ikenaga]

There's one convention in mathematics that's used consistently in
mathematics texts and in classrooms. Science, engineering and most other
disciplines also adhere to that convention.

Then there's Computer Science and programming. I haven't argued that those
languages that depart from the standard math convention do the right thing,
but I have argued that once done it won't be undone. So live with it.

Actually, I think I agree with the last paragraph (besides which,
this thread is getting kind of tired, like the tail end of a food
fight ). Given that spreadsheets have been doing this for years,
if Excel or OOcalc switched to the math convention, it might break
a lot of peoples' stuff and cause a lot of damage. That wouldn't be
right. But I don't feel bad about lobbying for the math point of
view, either.

The place where problems could occur is when someone who learned one
convention uses it in the wrong place. So discussions like this help
by publicizing the difference.


Bruce I.

 

[Dave Seaman]

As for your TI calculator, either you're pressing the key sequence

3 [+/-] [x^y] 2 [=] which returns +9

or

3 [x^y] 2 [=] [+/-] which returns -9

In either case you're applying an unambiguous and manual operator
precedence. If you turn on a TI calculator or clear the register so it
shows 0, then press [+/-], the register still displays and contains 0
(zero), not -0, and when you then key in 3, it displays and contains
3. There's no way to represent leading minus signs with a TI
calculator. Your arguments would be more persuasive if it weren't so
obvious you were dreaming some examples up on the fly.

Your last sentence is ironic considering that you jumped to an
incorrect conclusion concerning the kind of calculator I was
using. But I accept responsibility for not giving the model
number. And no, I'm not into making up stuff to win arguments,
if that's what you mean.

All the TI calculators we support are graphical algebraic entry
calculators, and the one I had at hand (on which I tried the
example I gave) was a TI-82. You're describing the keypresses
for an old-fashioned "scientific" one-line display calculator, I
guess. On my 82, I press

(-) 3 ^ 2 <enter> and get -9

(-) 3 <x^2> <enter> and get -9

Here the (-) key is the unary minus key, <x^2> is the squaring key
which produces a power (superscript) 2, and <enter> is the usual
<enter> key. The first sequence produces the display "-3^2" before
<enter> is pressed; in the second case, the "^2" is replaced by
a "real" power of 2.

And to add another data point, it is possible to duplicate this
computation on my HP48SX. I know what you're thinking; the HP
calculators use RPN. True, but the HP48 series calculators also have an
algebraic entry mode that allows you to enter

'-3^2' EVAL

which produces the result -9. As for that quoted expression, there
are at least two ways to enter it:

First method:
Press ' to enter algebraic mode, which produces '' and places
the insertion point between the quotes.

Press - (the subtraction sign).

Press 3.

Press the key marked y^x (y superscript x), which produces the
^ symbol.

Press 2.

Second method:
Same, except instead of pressing the subtraction symbol
followed by a 3, press 3 followed by the CHS (+/-) key.
Either way, the display reads '-3' at that point.

Thus, in this context, the unary minus and the binary minus are different
keys on the calculator but are interchangeable.

 

[Harlan Grove]

. . . I only draw from it that I was right in the first
place. The normal chalkboard, or academic math convention has been
deviated from. . . .
True.

. . . Some have given reasons for it like "since it's digital and it
can't hold a reciprocal it has to be that way" or something along those
lines. No one seems to really have any good reason.

More subtle: some people consider -3 unambiguously a single token
representing the reciprocal of 3. They generalize from that that -x is
unambiguously the reciprocal of x. Since "negative three" raised to the
second power is nine (+9), they figure that -x^2 should work similarly.

It's not standard, but it's not unreasonable, and it can be used
consistently. That there's then no point to y = -x^2 as opposed to y = x^2
doesn't matter to these people.

I'm sure it's as you say. The early coders just got it wrong and were
kind of stuck with it for legacy reasons. Sort of like the Y2K
situation a few years ago. Or the language applications just didn't
feel it necessary to get the order right, it was enough to just explain
in the reference manuals 'their' way of doing things.

More complicated. FORTRAN got it right from the beginning. Not surprising
since it was written by mathematicians and engineers. COBOL seems to have
been the first language to get it wrong. It was designed by business people
and the sainted Grace Hopper (note: I don't use emoticons to denote
sarcasm). They seem not to have been much concerned with the standard math
convention.

Like it or not, COBOL has been around a very long time, and there's still a
very large code base written in it. Excel, like it or not, followed COBOL's
convention. There's a very large 'code base' written in Excel. There's a
perception that it'd cost too much money to fix that code, and when
confronted by cost, business people ask "is the change necessary?" The
answer to that, like it or not, is *no*.

From my own development experience it's obvious to me that the reason
many of these people are holding some position that "it's just fine",
"that's the way it is", "user emptor", is that they don't have a lot
of math experience. . . .

....

Wrong. They just have real world experience in which cost is usually the
sole deciding factor. It costs less to teach prophylactic use of parentheses
than to change/correct these languages and application development systems
and all the code that runs under them. Also, many have experience with
Microsoft, its software and its attitude towards their own design decisions.
THIS IS NOT A BUG. FOR GOOD OR ILL, IT WAS INTENTIONAL. When Microsoft does
something on purpose, it stays done. See if you can detect any contrition in

http://support.microsoft.com/default.aspx?scid=kb;en-us;132686

To repeat: bitching, whining and moaning about this won't change a darn
thing. You are free to spend as much time engaged in these activities as you
like, but they'll be unproductive. Many people have learned the hard way the
futility of complaining about what Microsoft does and has done. Apparently
many more will have to learn the same lesson the same way.

. . . It's somewhat understandable in a language, but Excel is more
of an always-available do-everything super-calculator, not what I'd
consider a language. It should conform.

....

Why would it be more important for Excel, an end-user product *intended* to
be used by nontechnical people most of whom give less than a rat's back end
what mathematicians think, to adhere more strictly to standard conventions?
Why would it be OK for COBOL, some SQL variants and a few scripting
languages to retain this nonstandard operator precedence? If it's
'necessary' for one, it should be necessary for the other. OTOH, if the
world can cope with the languages remaining like this, it will continue
revolving about the sun if Excel remains unchanged.

Here's a little truth. Most people don't remember the operator precedence
they were taught in school. When in doubt, they look it up or test it out.

Here's an awkward bit of speculation. Now that computers have become nearly
ubiquitous in OECD countries, the great untutored masses now can calculate
on a daily basis. Perhaps this is a sign that most people either think the
existing convention isn't as useful as the Excel-like alternative or are
indifferent to the convention(s) employed in the software they use.

The world gets along just fine with driving on the left in some countries
and on the right in others. There's no obvious reason it can't also get
along with two broadly used operator precedence conventions as long as the
context governs the convention used. Current standard convention in
textbooks and articles, alternative used in 'business software'.

[In case you can't tell, I'm not being entirely serious in the last
paragraph, but other than a pathological need for rigid adherence to
orthodoxy, there's no necessary reason there must be one and only one
convention in use. It just means you need to check your convention before
using software. If there were one and only one notational form for inner
products, you all would have a stronger case for one and only one operator
precedence convention.]

 

[Acid Pooh]

It wouldn't be a non-issue - your statements still depend on an
implicit order of operator precedence.

No, they don't. This is a simple substitution of variables. Let y be
a variable independent of x. Then we can substitute x -> y^2 in the
expression x + (-x) = 0, since the expression is true for EVERY x.

Using your logic, strictly typographically, substitute x by y*2 in the
expression x^2.

y*2 isn't a term in the FOL. (y*2) is.

Are you arguing that the result is y*2^2 ==> y*4 (since I believe
everyone agrees that exponentiation has a higher precedence than
multiplication)??? If not, what would you do differently and why?

When you have a linear typographic notation with operators of different
precedence (e.g., Excel), one MUST use parentheses (or their equivalent)
to apply operations in the proper order.

Substitution of x by y^2 in the expression -x to produce -y^2 is *ONLY*
valid in a typographic system where exponentiation has a higher
precedence than monadic negation. If negation has higher precedence that
exponentiation, then the typographic substitution above is *only* valid
as

Uh... no, not really. Substitution of variables is often an axiom in
first-order languages. These languages apply to things much more
general than number systems.

-x ==> -(y^2)

Both are consistent typographical systems. Neither has any inherent
advantage over the other. Which you choose is, again, a matter of
convention.

To use that typography to argue for an implicit order of operator
precedence, that somehow your example resolves an ambiguity, is a
tautology, nothing more.

Stop being difficult. You asked for a citation and you got it. And
you got me to explain how substitution of variables work in the FOL.

'cid 'ooh

 

[Harlan Grove]

or if you bought a car and noticed it was low on oil
and you topped it off and the pistons seized
and you later found out that this particular car
only uses synthetic oil and you should have checked
that out before you put oil in it.

If it stated unambiguously in the owners manual that this was so, and if
there were a warning on the dip stick or oil cap, it'd be entirely my own
fault.

or you bought an instant lottery ticket and
scratched off the boxes with a quarter
and won $20,000
and took it in, and they said you were supposed to
scratch if off with an "approved scratcher"
so your ticket is invalid.

If 4-point type in the back side of the ticket stated this requirement
clearly if nearly illegibly, again it'd be my own fault.

As for your first example, that's why lawyers make the big bucks.

 

[Harlan Grove]

As I have noted before, this is incredible hubris on your part.

If I were the programmer who wrote this, you'd have a point. However, I'm
just a 'user' who's used this stuff long enough to know how to avoid
stepping in the metaphorical piles of dung.

If you use Microsoft software, you'd better read the documentation, or
you'll get bit in the butt. Complaining about how sore your butt is will get
you as much sympathy from Microsoft as the death cries of its erstwhile
competitors. Some of us learned to adapt to this state of affairs more
quickly than others.

Software professionals create tools to serve the client. . . .

There was precedent for the operator precedence convention Microsoft adopted
for Excel: COBOL. Maybe it only needs to be pointed out to a few
professional mathematicians that there was and is a lot of business software
written in COBOL, that some of the COBOL developers would become Excel
developers, and thus adopting the same operator precedence convention would
be rather an obvious thing to do in Excel. Do I need to make this simpler
for you to understand that Microsoft could have done *exactly* what you're
saying it should have done - serve the client? Or are you deluded enough to
believe that Microsoft considers the few thousand people who care about
'correct' operator precedence more important than the millions of users who
just don't care or may have become used to something else?

. . . It is *not* the responsbility of the client to adapt to the
programmers' sloppy design. . . .

They don't let you out into the real world much, do they?

If Microsoft were selling their services to no more than a few hundred
customers and faced serious competition, then you might have a point.
However, Microsoft sells millions of license units annually. The market has
voted: their customers are perfectly willing to adapt. I know it's hard for
some mathematicians to have to deal with empirical data, but you may get the
hang of it one day.

Documentation of this one exception does *not* excuse its
implementation, which surely causes undetected errors and/or generates
many hours spend on debugging.

Believe me, there are many other common sources of errors in Excel.

Anyway, to repeat something I've written before, as far as Microsoft is
concerned (and I'm not writing *for* them, I'm writing *about* them as a
long-time, interested observer) this is not a *BUG*. It's a design decision.
I agree that it's an unfortunate design decision, but bad designs that are
used in tens of millions of applications tend to remain unchanged. If you
don't understand why this is so, I can only suggest you seek out colleagues
in the Economics department who may be able to explain if for you.

 

[mangesh_yadav]

And if that is really the case with Fred, then its just a question o
putting brackets at the appropriate place.

But, I think that instead of creating such a ruckus, it would have bee
wiser to post the bug, rather than wasting everyone's time.

- Manges

 

[Harlan Grove]

Your last sentence is ironic considering that you jumped to an
incorrect conclusion concerning the kind of calculator I was
using. But I accept responsibility for not giving the model
number. And no, I'm not into making up stuff to win arguments,
if that's what you mean.

....

I appologize for that, and I'll take your word. I was assuming old fashioned
single number display calculators (no need for anything newer).

 

[Acid Pooh]

So you substitute just one convention by another to clarify?

Yes. All languages are conventional.

But, given the context (a person asked me to justify the claim that
x^2 + (-x^2) = 0 from more-or-less the order axioms), I think the FOL
has primacy.

Yup, very slippery.

I'm sure you get it, but just to be clear, for the benefit of those
that don't: x + (-x) = 0 is an abbreviated notation for Ax(x+ (-x) =
0), where Ax means "for all x." A straightfoward application of
Universal Instantiation gives us the result.

'cid 'ooh

 

[Phil Carmody]

...

Laugh all you want, Postgresql shares this operator precedence.

http://www.postgresql.org/docs/7.2/interactive/sql-precedence.html

"This may lead to non-intuitive behavior;"
Joy joy.

MySQL's saner, which is what i use:
http://dev.mysql.com/doc/mysql/en/Operator_Precedence.html
Thou quite why bitwise XOR is so vastly higher in priority than
other bitwise operators, I have no idea.

Phil

 

[Harlan Grove]

Thou quite why bitwise XOR is so vastly higher in priority than
other bitwise operators, I have no idea.

....

Maybe 'cause someone thought it was exponentiation.

 

[Phil Carmody]

...

Maybe 'cause someone thought it was exponentiation.

The same deviant thought went through my mind.
However, I purged it quickly, as it made me nauseous

Phil