|
Excel Math Bug
"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. |
Excel Math Bug
On Tue, 3 Aug 2004 20:08:14 -0700, Harlan Grove wrote:
"Kevin O'Neill" wrote... - x = (-1)x . ... 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 -- Dave Seaman Judge Yohn's mistakes revealed in Mumia Abu-Jamal ruling. <http://www.commoncouragepress.com/index.cfm?action=book&bookid=228 |
Excel Math Bug
On Tue, 03 Aug 2004 14:26:52 -0700, Alan Beban 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. 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. |
Excel Math Bug
On Tue, 3 Aug 2004 19:29:08 -0700, "Harlan Grove" wrote:
(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. |
Excel Math Bug
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. |
Excel Math Bug
<many good points snipped for brevity
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 |
Excel Math Bug
"Myrna Larson" wrote in message
... On Tue, 3 Aug 2004 19:29:08 -0700, "Harlan Grove" wrote: (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. 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. |
Excel Math Bug
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 ================================================= "fred" wrote in message ... <many good points snipped for brevity 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 |
Excel Math Bug
"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ...
"Acid Pooh" wrote in message om... JE McGimpsey wrote in message ... In article , Dave Seaman wrote: By the definition of unary minus, -x^2 is the number such that x^2 + (-x^2) = 0. 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 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 |
Excel Math Bug
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? |
Excel Math Bug
|
Excel Math Bug
"r.e.s." writes:
"Harlan Grove" wrote 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. 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 -- 1st bug in MS win2k source code found after 20 minutes: scanline.cpp 2nd and 3rd bug found after 10 more minutes: gethost.c Both non-exploitable. (The 2nd/3rd ones might be, depending on the CRTL) |
Excel Math Bug
"Dik T. Winter" writes:
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 -- 1st bug in MS win2k source code found after 20 minutes: scanline.cpp 2nd and 3rd bug found after 10 more minutes: gethost.c Both non-exploitable. (The 2nd/3rd ones might be, depending on the CRTL) |
Excel Math Bug
Dave Seaman writes:
On Tue, 3 Aug 2004 20:08:14 -0700, Harlan Grove wrote: "Kevin O'Neill" wrote... - x = (-1)x . ... 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. 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 -- 1st bug in MS win2k source code found after 20 minutes: scanline.cpp 2nd and 3rd bug found after 10 more minutes: gethost.c Both non-exploitable. (The 2nd/3rd ones might be, depending on the CRTL) |
Excel Math Bug
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 Bruce Ikenaga wrote: On Tue, 03 Aug 2004 14:26:52 -0700, Alan Beban 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. 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. |
Excel Math Bug
JE McGimpsey wrote in message ...
In article , (Acid Pooh) wrote: 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. Well, since if I were substituting x+3 for x, the result would be -(x+3) 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 |
Excel Math Bug
In article , Bruce
Ikenaga wrote: 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 |
Excel Math Bug
In article Phil Carmody writes:
"Dik T. Winter" writes: 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. Interesting. Just as in what I did learn, no negation. -- dik t. winter, cwi, kruislaan 413, 1098 sj amsterdam, nederland, +31205924131 home: bovenover 215, 1025 jn amsterdam, nederland; http://www.cwi.nl/~dik/ |
Excel Math Bug
In article ,
(Acid Pooh) writes: .... 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. Substitute x^2 for x. 'cid 'ooh But you said yourself that -x is "...the number", suggesting that -x can be thought of as a notation for a single object, the - is tightly bound to the x, and hence -x^2 is really (-x)^2. Similarly, in the number "-5", the - is not a unary operator, it's just part of the imprecise alphabet used to name objects. I'm not surprised that many people see -5^2 as (-5)^2. APL uses "high minus" for "minus as part of the name of a number", and J (son of APL) uses underline: -5^2 _25 _5^2 25 This avoids ambiguity, not to mention unpleasant arguments. \begin{cute quotes} % Then I realized that I had spelled '-' wrong. John Whitmore [on learning APL] % We exchanged many frank words in our respective languages. Peter Cook, Beyond the Fringe % \end{cute quotes} -- J.E.H.Shaw [Ewart Shaw] ick TEL: +44 2476 523069 Department of Statistics, University of Warwick, Coventry CV4 7AL, UK http://www.warwick.ac.uk/statsdept http://www.ewartshaw.co.uk 3 )@|:"2^:2))&.@]^:(i.@[) <#:3 6 2 |
Excel Math Bug
On 04 Aug 2004 12:18:35 +0300, Phil Carmody wrote:
Dave Seaman writes: On Tue, 3 Aug 2004 20:08:14 -0700, Harlan Grove wrote: "Kevin O'Neill" wrote... - x = (-1)x . ... 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. If you assume the conclusion you wish to justify. AKA begging the question. -x^2 = (-1)x^2 I am not assuming anything. I merely observe how symbols are used in textbooks on mathematics. If one asserts that unary minus has higher precedence, then -x^2 = ((-1)x)^2 And one would be wrong. It's consistent. It doesn't change the mathematics at all, it's simply a matter of expression parsing. Claiming that triangles have four sides doesn't change the mathematics, either. -- Dave Seaman Judge Yohn's mistakes revealed in Mumia Abu-Jamal ruling. <http://www.commoncouragepress.com/index.cfm?action=book&bookid=228 |
Excel Math Bug
On Wed, 04 Aug 2004 02:48:08 -0700, Alan Beban wrote:
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. Only if you ignore Lang's _Algebra_, which I have cited twice in this thread. 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. Stating that the elements of a polynomial ring A[X] are linear combinations of the basic monomials X^k is an example that describes the convention for the order of precedence, not an example of usage and tacit accpetance of that convention. It makes clear that -X^2 as a member of A[X] can only mean (-1)*X^2, where X^2 is one of the primitive monomials X^k, and -1 is a member of the ring A. It can't possibly mean (-X)^2, because -X and (-X)^2 are not members of the set of primitive monomials { X^k : k in N }. -- Dave Seaman Judge Yohn's mistakes revealed in Mumia Abu-Jamal ruling. <http://www.commoncouragepress.com/index.cfm?action=book&bookid=228 |
Excel Math Bug
"fred" wrote...
"Myrna Larson" wrote... .... According to my Multiplan 4.2 manual, the order is "Sign (in other words, whether the number is positive or negative) Percentage Exponentiation .... which, I believe, is the same as Excel, no? So -3^2 would be +9. I suspected as much. Excel seems to have been Multiplan's offspring. Not surprising no one caught this in Multiplan given its low market share. Well, yes, but you're coming from the attitude that since it's listed a certain way in the manual that it's okay. . . . To the extent it's clearly documented, it *is* OK. Well, at least not a bug. Just a really bad design decision. Posted Via Nuthinbutnews.Com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY ** ---------------------------------------------------------- http://www.nuthinbutnews.com |
Excel Math Bug
"Harlan Grove" wrote in message ...
"fred" wrote... ... 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 ... A distinction non-programmers often don't appreciate is that a program (or a programming language) have to PICK A FIXED DEFINITION. In mathematical prose (for human consumption) there are usually context clues that resolve what would otherwise be ambiguous, so -5^2 could be interpreted one way and -x^2 the other way. Unfortunately operator precedence rules are not always well documented; worse perhaps, many people don't realise that they need to be documented to a level of detail that can only be appreciated by looking at various contrasting examples, such as Dik Winter's 8 - 3 + 4 which a literal interpretation of the often carelessly stated Aunt Sally rules renders as 8-(3+4) and not (8-3)+4. In other words, both PRECEDENCE and ASSOCIATIVITY matter, and each class may include SEVERAL operators, so a simple linear list like PEMDAS can't possibly capture it all -- it must be seen as no more than a mnemonic device to help recall the full definition. Michel. |
Excel Math Bug
|
Excel Math Bug
Bruce Ikenaga wrote...
.... . . . 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 . . . .... 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. 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. 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. .... That was a grammar for a middle weight console calcuator named hoc. The authors refer to the standard Unix bc tool which usese the opposite precedence order for negation and exponentiation. I forget whether the authors mentioned this difference between hoc and bc. . . . 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. This shouldn't be a deciding factor. Once known, prophylactic measures can be taken to handle this. There's a much better reason to use real stats packages: they make incremental refinement of analysis much easier. Why don't you ask some of your stats colleagues to compare the ease of performing step-wise regression in Excel vs, say, R. Or using logit or probit models in both. However, more to the point may be http://www.theregister.co.uk/2004/07...vanishing_dna/ |
Excel Math Bug
Dave Seaman wrote...
.... Stating that the elements of a polynomial ring A[X] are linear combinations of the basic monomials X^k is an example that describes the convention for the order of precedence, not an example of usage and tacit accpetance of that convention. It makes clear that -X^2 as a member of A[X] can only mean (-1)*X^2, where X^2 is one of the primitive monomials X^k, and -1 is a member of the ring A. It can't possibly mean (-X)^2, because -X and (-X)^2 are not members of the set of primitive monomials { X^k : k in N }. This may be the core of the problem. Those with a thorough mathematical background would think of variables first: -x^2 - polynomials, so the - and x are separate tokens - -(x^2). On the other hand, those who avoided math classes after they'd satisfied whatever minimal requirements were imposed upon them would likely think of numbers first: -3^2 - -3 is an atomic whole - (-3)^2. Both are reasonable. Both are consistent. Both address the unfortunate fact that mathematicians are unspeakably lazy and choose to represent arithmetic sign in numbers with the same character they use to represent the diadic subtraction operator and the monadic negation operator. The same is mercifully not the case with the multiplicative analog since, e.g., 1/4 can be represented by 0.25. Just think what fun we'd have if it were commonly written as /4. That the variables-inspired convention is standard among mathematicians is/was, unfortunately, not immediately relevant or persuasive to many programmers who write/wrote programming languages or spreadsheets. This state of affairs may be unfortunate, but it's about time mathematicians, statisticians, scientists, engineers and any other nonprogrammers who use computers to calculate realize that there are lots of languages and programs that have adopted other operator precedence rules than those with which they're familiar. |
Excel Math Bug
() wrote in message ...
In article , (Acid Pooh) writes: ... 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. Substitute x^2 for x. 'cid 'ooh But you said yourself that -x is "...the number", suggesting that -x can be thought of as a notation for a single object, the - is tightly bound to the x, and hence -x^2 is really (-x)^2. Uhm, sure. -x is "the number" in the relevant sense. But the "x" in the expression "-x" is a variable. You can substitute one variable for another and obtain a legal expression. Notice that I didn't say "square x," or that if I were to have said "substitute x by y^2," this would be a non-issue. 'cid 'ooh |
Excel Math Bug
Harlan Grove wrote:
That the variables-inspired convention is standard among mathematicians is/was, unfortunately, not immediately relevant or persuasive to many programmers who write/wrote programming languages or spreadsheets. This state of affairs may be unfortunate, but it's about time mathematicians, statisticians, scientists, engineers and any other nonprogrammers who use computers to calculate realize that there are lots of languages and programs that have adopted other operator precedence rules than those with which they're familiar. As I have noted before, this is incredible hubris on your part. Software professionals create tools to serve the client. It is *not* the responsbility of the client to adapt to the programmers' sloppy design. Excel is *not* a programming language for at least 90% of its users. In all other aspects, Excel conforms to *the* standard order of precedence which you will find in *any* math book or science or engineering book or article which uses mathematical notation. Documentation of this one exception does *not* excuse its implementation, which surely causes undetected errors and/or generates many hours spend on debugging. -- Stephen J. Herschkorn |
Excel Math Bug
|
Excel Math Bug
That the variables-inspired convention is standard among
mathematicians is/was, unfortunately, not immediately relevant or persuasive to many programmers who write/wrote programming languages or spreadsheets. This state of affairs may be unfortunate, but it's about time mathematicians, statisticians, scientists, engineers and any other nonprogrammers who use computers to calculate realize that there are lots of languages and programs that have adopted other operator precedence rules than those with which they're familiar. I agree on a pragmatic level "math people beware", but on principle, the programmers didn't do it right. If you dear Aunt Sally died and left a will that said "I leave all my money to Harlan and all my property to his sister" and you knew she had $100,000 in the bank and a huge chest of coins in the attic and the lawyer told you that in your state the term "money" includes "paper bills" only and that coins and bank accounts are considered "property" and you were left with the $78 she had laying on her dresser, how easily would you accept lack of convention then? 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. 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. Maybe you are not saying "it's fine", or are you? |
Excel Math Bug
"Dik T. Winter" wrote in message ...
In article (Acid Pooh) writes: JE McGimpsey wrote in message ... In article , (Acid Pooh) wrote: 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. How does that clarify the meaning? Well, since if I were substituting x+3 for x, the result would be -(x+3) 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. Well, my point here was that "x + 3" isn't strictly speaking a term in the FOL, whereas "(x+3)" is. Neither is x^2, for that matter, but x^2 is usually taken to be abbreviated notation for (x*x). We can come up with some nice abbreviated notation for (x + 3)--say, "z"--and substitution yields -z. So what do you get if you substitute x^2 for x in "-x" ? Notice that I didn't say "square x." For all it matters, I could have said substitute y^2 for x. Or, if you'll allow me the convenience of working in the reals, consider that x = "Sqrt(x)^2". More formally: this is a substitution of _variables_ in the sense of the first order logic. It doesn't matter what 'x' is in the formula x + (-x) = 0 is. It might as well be "x^2." Kind of slippery, huh? 'cid 'ooh |
Excel Math Bug
In article (Acid Pooh) writes:
"Dik T. Winter" wrote in message ... In article (Acid Pooh) writes: JE McGimpsey wrote in message ... In article , (Acid Pooh) wrote: 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. How does that clarify the meaning? Well, since if I were substituting x+3 for x, the result would be -(x+3) 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. Well, my point here was that "x + 3" isn't strictly speaking a term in the FOL, whereas "(x+3)" is. Neither is x^2, for that matter, but x^2 is usually taken to be abbreviated notation for (x*x). We can come up with some nice abbreviated notation for (x + 3)--say, "z"--and substitution yields -z. So you substitute just one convention by another to clarify? So what do you get if you substitute x^2 for x in "-x" ? Notice that I didn't say "square x." For all it matters, I could have said substitute y^2 for x. Or, if you'll allow me the convenience of working in the reals, consider that x = "Sqrt(x)^2". More formally: this is a substitution of _variables_ in the sense of the first order logic. It doesn't matter what 'x' is in the formula x + (-x) = 0 is. It might as well be "x^2." Kind of slippery, huh? Yup, very slippery. -- dik t. winter, cwi, kruislaan 413, 1098 sj amsterdam, nederland, +31205924131 home: bovenover 215, 1025 jn amsterdam, nederland; http://www.cwi.nl/~dik/ |
Excel Math Bug
"Phil Carmody" wrote...
.... 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/i...recedence.html Posted Via Nuthinbutnews.Com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY ** ---------------------------------------------------------- http://www.nuthinbutnews.com |
Excel Math Bug
"Dave Seaman" wrote...
.... I and others have asked repeatedly for a single instance in any mathematical text where -x^2 was interpreted as (-x)^2, and not a single instance has been cited. There are not two conventions. There is only one. Case closed. 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. Posted Via Nuthinbutnews.Com Premium Usenet Newsgroup Services ---------------------------------------------------------- ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY ** ---------------------------------------------------------- http://www.nuthinbutnews.com |
Excel Math Bug
"Dave Seaman" wrote in message ...
On 4 Aug 2004 09:47:04 -0700, Harlan Grove wrote: Dave Seaman wrote... On Tue, 3 Aug 2004 20:08:14 -0700, Harlan Grove wrote: "Kevin O'Neill" wrote... - x = (-1)x . ... 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 You're fixated on needing to define negation in terms of multiplication. That's one convention. Another completely consistent one would be -x^2 = (0-x)^2 = 0^2 - 2 * 0 * x + x^2 = x^2 = 0 "Contrariwise," continued Tweedledee, "if it was so, it might be; and if it were so, it would be; but as it isn't, it ain't. That's logic." I and others have asked repeatedly for a single instance in any mathematical text where -x^2 was interpreted as (-x)^2, and not a single instance has been cited. There are not two conventions. There is only one. Case closed. -- Dave Seaman Judge Yohn's mistakes revealed in Mumia Abu-Jamal ruling. <http://www.commoncouragepress.com/index.cfm?action=book&bookid=228 ================================================= Well said. Kevin O'Neill ================================================= |
Excel Math Bug
"Stephen J. Herschkorn" wrote in message ...
Harlan Grove wrote: That the variables-inspired convention is standard among mathematicians is/was, unfortunately, not immediately relevant or persuasive to many programmers who write/wrote programming languages or spreadsheets. This state of affairs may be unfortunate, but it's about time mathematicians, statisticians, scientists, engineers and any other nonprogrammers who use computers to calculate realize that there are lots of languages and programs that have adopted other operator precedence rules than those with which they're familiar. As I have noted before, this is incredible hubris on your part. Software professionals create tools to serve the client. It is *not* the responsbility of the client to adapt to the programmers' sloppy design. Excel is *not* a programming language for at least 90% of its users. In all other aspects, Excel conforms to *the* standard order of precedence which you will find in *any* math book or science or engineering book or article which uses mathematical notation. Documentation of this one exception does *not* excuse its implementation, which surely causes undetected errors and/or generates many hours spend on debugging. -- Stephen J. Herschkorn ================================================= Well said. Kevin O'Neill ================================================= |
Excel Math Bug
In article , "Harlan Grove"
wrote: 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/i...recedence.html As does mysql http://dev.mysql.com/doc/mysql/en/Op...recedence.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. |
Excel Math Bug
On Wed, 04 Aug 2004 11:16:57 -0700, Harlan Grove wrote:
Bruce Ikenaga wrote... ... . . . 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 . . . ... 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. |
Excel Math Bug
On Wed, 04 Aug 2004 18:56:21 -0700, Harlan Grove wrote:
"Dave Seaman" wrote... ... I and others have asked repeatedly for a single instance in any mathematical text where -x^2 was interpreted as (-x)^2, and not a single instance has been cited. There are not two conventions. There is only one. Case closed. 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. |
| All times are GMT +1. The time now is 10:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com