Excel Math Bug

H

Harlan Grove

Kevin O'Neill said:
The issue is far simpler than most other posts/responses seem to
indicate: it comes from the order-of-operation rules of arithmetic,
and "negation" should be regarded as multiplication under these rules
because, for any real number x

- x = (-1)x .
....

True, but also - x = 0 - x. Which is the one to use?
It is truly unfortunate that ANY computer languages or programs would not
conform to the order-of-operation rules.

Agreed, but more than a few don't. As stated elsewhere in this thread,
VisiCalc, the first widely used spreadsheet, used simple left-to-right
evaluation with no operator precedence. APL, on the other hand, uses simple
right-to-left evaluation with no operator precedence. COBOL and it seems
also REXX in addition to Excel use standard operator associativity and
operator precedence *except* for negation having higher precedence than
exponentiation. Finally, there are the mainstream languages that adopted, to
the extent possible, standard math/science textbook operator precedence.

It's unfortunate COBOL, REXX and Excel do this, but they do exist and they
can't be changed without breaking existing applications. Also, the existence
of at least 4 different arithmetic evaluation procedures in programming
languages (broadly defined to include spreadsheets as a functional variety)
should give pause to anyone attempting to use any of them to program.

When programming computers, know your tools.

Is there any reason why it works this way? What I figure is that someone a
long time ago (maybe the COBOL designers) saw -3 ** 2 or something similar
in FORTRAN and decided that this 'naturally' read as negative three (as a
term in and of itself) raised to the second power. They generalized from
this, and -x ** 2 would be negative x (also as a term in and of itself)
raised to the second power. It may go counter to textbook convention, but
from the point of view of nonmathematicians it's not a completely
unreasonable approach.
 
F

Frank_Hamersley

"Harlan Grove" wrote >
When the two, Excel and VBA, were merged, the inconsistency became truly exquisite.
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.

IMO this sort of inconsistency is symptomatic in many ways of Microsoft's
software engineering - although I do accept it is quite possibly an industry
wide malaise rather than MS specific.

When the issue was first identified the bitter pill should have been
swallowed and the "problem" fixed. Sure there would have been some grief,
but it would have been a lot less than would arise were an attempt made to
address it today.

This lack of guts (ie.expediency rules over rationality) IMO underpins the
even more "exquisite" hacks on IE and other products - it makes the
Trustworthy initiative quite laughable because the focus seems not to
unequivocally fix the problems at the source, but to determine the most
expedient way to neutralise a threat by working out which objects can be
trusted to what degree. Sure defensive coding is desirable in each and
every object, but that is never a justification to leave an aberrant object
untouched.

Apols for the sermon, Frank.
 
D

Dave Seaman

True, but also - x = 0 - x. Which is the one to use?

It doesn't matter. Both are correct, and both lead to the same conclusion.

-x^2 = (-1)x^2
= (0-1)x^2
= 0x^2 - 1x^2
= 0 - x^2
= -x^2
<= 0
 
B

Bruce Ikenaga

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.

First, we can agree that a convention on operator precedence is exactly
that --- a convention. A particular convention is not demanded by the
logic of mathematics.

Someone noted that the definition of "-x" as the additive inverse of an
element x in a ring does not by itself determine the meaning of an
expression such as "-x^2" --- that the meaning of "-x^2" depends on one's
operator precedence convention. That's correct. There are two operators in
the expression "-x^2", and you have to decide by convention which will be
done first.

It IS the case that there is a generally accepted convention for operator
precedence in mathematics. For the elementary operations, it is (highest
to lowest):

exponentiation
unary minus
multiplication and division (left associative)
addition and subtraction (left associative)

(People sometimes forget the associativity and just remember "My Dear Aunt
Sally" or something like that, but the associativity is part of the
precedence rules.) It is taught this way --- certainly in the U.S. --- I
would guess around middle or high school. (By the way, for people
complaining about a lack of citations, this may be one reason why no one
gave any earlier. I started hunting around and found that the most
elementary textbook I have here at home is at the calculus level.)

In other words, any mathematician would say that -3^2 is -9 and the
graph of y = -x^2 is a parabola opening downward. (I'm a math professor,
So I guess I'm citation #1. :) In fact, pointing out that "-3^2 = -9,
not 9" is something most of us do to point out a "standard mistake".

But I guess people want "real" citations. If the following aren't
satisfactory, when I go into my office I'll get out some high school
algebra books and come up with all the citations anyone wants. (If people
want to check this themselves, that's where to look --- algebra books at
the high school or middle school level.)

"Brief Calculus" by Ron Larson and Bruce Edwards (6th edition). The order
of operations is listed on page 72, as I've given it above. To pick an
example at random, on page 78, problem 57 shows the graph of y = -x^2 + 2
--- a parabola opening downward.

I looked in calculus books by Anton, Stewart, and Smith and Minton and
easily found examples or problems which make it clear that "-x^2" means
"square first, then negate". For instance, the example cited by someone
earlier of (the normal probability density function) y = ce^(-x^2/2)
occurs in nearly all calc books (e.g. Smith and Minton, 2nd edition, page
469).

Other examples of this convention are readily available. Mathematica tells
me that -3^2 is -9. So does maxima. So does gap. So does my TI calculator.
For programmers, check out the yacc grammar on page 250 of "The UNIX
Programming Environment" by Brian Kernighan and Rob Pike. Note that
exponentiation has higher precedence than unary minus.

Note, by the way, that if "-x^2" were interpreted to mean "(-x)^2", then
there would never be any reason to write "-x^2", since it would be simpler
to write "x^2" instead. Moreover, if you wanted to square first, then
negate, you'd need to write the cumbersome "-(x^2)".

Yeah, it's a convention, but there IS an established convention in math,
and it says -3^2 is -9. If Excel does it differently, they're using a
different convention --- and I can understand how that would happen, in
trying to remain compatible with older software. My guess is one of the
original spreadsheet authors screwed it up, and people have been trying to
maintain compatibility since then. Actually, I'm glad this came up --- the
stat people in our department were tossing around the idea of using Excel
in their courses instead of a stat package, but this is a reason for
rejecting that idea. If Excel doesn't follow such a standard mathematical
convention, I don't think I'd want students using it in our courses.

Bruce I.
 
M

Myrna Larson

(Does anyone know how Multiplan handled -3^2?)

According to my Multiplan 4.2 manual, the order is

"Sign (in other words, whether the number is positive or negative)
Percentage
Exponentiation
Multiplication and Division
Addition and Subtraction
Logical operations"

which, I believe, is the same as Excel, no? So -3^2 would be +9.
 
M

Myrna Larson

the stat people in our department were tossing around the idea of using Excel
in their courses instead of a stat package, but this is a reason for
rejecting that idea. If Excel doesn't follow such a standard mathematical
convention, I don't think I'd want students using it in our courses.

There are other reasons to question the use of Excel as your main statistics
package. This issue is probably one of the easiest to deal with.
 
F

fred

Yeah, it's a convention, but there IS an established convention in math,
and it says -3^2 is -9. If Excel does it differently, they're using a
different convention --- and I can understand how that would happen, in
trying to remain compatible with older software. My guess is one of the
original spreadsheet authors screwed it up, and people have been trying to
maintain compatibility since then. Actually, I'm glad this came up --- the
stat people in our department were tossing around the idea of using Excel
in their courses instead of a stat package, but this is a reason for
rejecting that idea. If Excel doesn't follow such a standard mathematical
convention, I don't think I'd want students using it in our courses.

Bruce I.

I'm the one who brought this up in the first place and although this
discussion
has been quite interesting I only draw from it that I was right in the first
place. The normal chalkboard, or academic math convention has been deviated
from. In my mind this is wrong. 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.
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.

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.
They are like the dozens (or hundred) of programmers I've known over the
years who code up some screen or application that is totally unusable
because they are making ridiculous assumptions about user expectations or
user behavior. They're just coders and may be good at coding, but in the
example at hand, a subtlety was missed.

It's apparent now that many applications and/or languages don't use the
standard math conventions. Why there are so many voices saying that's okay
is mystifying. It's not okay. It's sad. But unfortunately that's what we
are stuck with. 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.

I agree with you, this is the last straw for me and I'm going to get some
real math application -- maybe Mathematica. Excel is wonderful for many
things -- charting, pivot tables, quick analyses -- and actually, I believe
the spreadsheet in general was responsible in large part for the growth of
PCs in the early years, but that is doesn't conform to academic math
conventions is unfortunate, and this type of thing is endemic with
consumer-grade software. Mathematica seems to be a serious,
industrial-strength application developed especially for mathematicians.
The $2000 it cost me to learn this lesson was far more than the $300 I was
considering spending for Mathematica.

Fred
 
F

fred

Myrna Larson said:
According to my Multiplan 4.2 manual, the order is

"Sign (in other words, whether the number is positive or negative)
Percentage
Exponentiation
Multiplication and Division
Addition and Subtraction
Logical operations"

which, I believe, is the same as Excel, no? So -3^2 would be +9.

Well, yes, but you're coming from the attitude that since it's listed a
certain way in the manual that it's okay. But there has been an order
followed for decades (centuries?) in math classes and journals and textbooks
that is no longer followed, and each language or application is arbitrarily
deciding how to handle it. It may be considered okay because it's
relatively common, but that's kind of a weak basis.
 
A

Aristotle Polonium

SNIP
something along those lines. No one seems to really have any good reason.
SNIP


=================================================

The "good reason" has been posted.

Unfortunately, it has not yet been recognized.

Kevin O'Neill

=================================================
 
A

Acid Pooh

Vasant Nanavati said:
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.

Substitute x^2 for x.

'cid 'ooh
 
F

fred

The "good reason" has been posted.
Unfortunately, it has not yet been recognized.

Well, what was it? I didn't hear one.

Why must a digital device use a different order of operation?
 
J

JE McGimpsey

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.

Substitute x^2 for x.[/QUOTE]

Well, since if I were substituting x+3 for x, the result would be

-(x+3)

I suppose that substituting x^2 would have to result in

-(x^2)

right? Typographically, at least, that's not the same as -x^2...
 
P

Phil Carmody

r.e.s. said:
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.

OMFG!

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.

Phil
 
P

Phil Carmody

Dik T. Winter said:
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.

Thanks for that insight, Dik. The "bodmas" I was taught was "brackets,
of (which translated into function calls and powering (cube of)),
division, multiplication, addition subtraction", but it was taught
with d&m and a&s as level in precedence. I have no idea if anyone was
taught bodmas without the d&m and a&s riders, perhaps they were, in
which case, there would be equivalent differences as your (snipped)
example.

Phil
 
P

Phil Carmody

Dave Seaman said:
It doesn't matter. Both are correct, and both lead to the same conclusion.

If you assume the conclusion you wish to justify. AKA begging the question.
-x^2 = (-1)x^2

If one asserts that unary minus has higher precedence, then

-x^2 = ((-1)x)^2

It's consistent. It doesn't change the mathematics at all, it's simply
a matter of expression parsing.

I was reading a mathematical paper this morning which had variables a, b,
e, p, q, and x in some of its equations. It also used concatenation to
represent multiplication, such as 'pq'. I wonder what would have happened
if the author had wanted to multiply the product of e, x, and p by the
sum of a and b. I can only conclude this : exp(a+b). He came _very_ close
to that expression.

Phil
 
A

Alan Beban

Thank you. The citation below to "Brief Calculus" is the first and only
one in this thread of a source, outside of programming, for the order of
precedence between negation and exponientation.

I appreciate your recognition of the distinction between a source
describing the convention for the order of precedence, on the one hand,
and examples of usage and tacit acceptance of that convention, on the
other.

It was also helpful, to me at least, to see the clear statement that it
is certainly a matter of convention; a conclusion that, as I previously
stated, seems to me self-evident, notwithstanding at least one
apparently misguided attempt in this thread to prove from first
principles and without resort to any convention that -x^2 = -(x^2).

Thanks again,
Alan Beban
 
A

Acid Pooh

JE McGimpsey said:
Substitute x^2 for x.

Well, since if I were substituting x+3 for x, the result would be

-(x+3)
[/QUOTE]

Well, I suppose you mean you substituted (x + 3) for x.
I suppose that substituting x^2 would have to result in

-(x^2)

right? Typographically, at least, that's not the same as -x^2...

Nope--you'd get that if you substituted (x^2) for x. *Sigh* This is so pedantic.

'cid 'ooh
 
H

Helmut Richter

It IS the case that there is a generally accepted convention for operator
precedence in mathematics. For the elementary operations, it is (highest
to lowest):

exponentiation
unary minus
multiplication and division (left associative)
addition and subtraction (left associative)

I would add two things: exponentiation order and a distinction between
multiplication with and without sign:
exponentiation (right associative)
unary minus
multiplication written without sign
multiplication with sign and division (left associative)
addition and subtraction (left associative)

The first addition says that a^b^c means a^(b^c) (occurs only in
machine script, not in handscript or print), and the second that a/bc
means a/(bc) whereas a/b·c means (a/b)·c.

Moreover, logicians have the convention that an arrow meaning
implication is right associative.

Helmut Richter
 
D

Dik T. Winter

Thanks for that insight, Dik. The "bodmas" I was taught was "brackets,
of (which translated into function calls and powering (cube of)),
division, multiplication, addition subtraction", but it was taught
with d&m and a&s as level in precedence.

Interesting. Just as in what I did learn, no negation.
 
D

Dik T. Winter

How does that clarify the meaning?
Well, I suppose you mean you substituted (x + 3) for x.

Good. Substitute x+3 for x and we get -x+3. And so what?
It only shows that substitution without surrounding braces leads to
an expression that is quite different.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top