Order of Operations and Negation in Excel
Date: 01/13/2000 at 14:18:46 From: Peter Subject: Order of operations : negation vs exponentiation In contrast to the HP48G calculator User's Guide, from which you quote in your answer to an earlier question, Microsoft Excel seems to confirm students in their belief that -4^2 = 16 instead of -16. Excel does indeed first negate the 4 and then raise the result to the power of 2. HP48G User Guide: The order of operations, from highest to lowest, is given below. Functions with the same precedence are performed from left to right. 1. Expressions within parentheses. Expressions within nested parentheses are evaluated from inner to outer. 2. Prefix functions (such as sin, ln, ...). 3. Postfix functions (such as ! (factorial)). 4. Power (^) and square root. 5. Negation (-), multiplication, and division. 6. Addition and subtraction. Excel: If you combine several operators in a single formula, Microsoft Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence (for example, if a formula contains both a multiplication and division operator) Microsoft Excel evaluates the operators from left to right. To change the order of evaluation, enclose the part of the formula to be calculated first in parentheses. Operator Description : colon , comma single space Reference operators – Negation (as in –1) % Percent ^ Exponentiation * and / Multiplication and division + and – Addition and subtraction & Connects two strings of text (concatenation) = < > <= >= <> Comparison Now I'd like to know which one is right ie: what do professional mathematicians do?
Date: 01/13/2000 at 20:18:46 From: Doctor Peterson Subject: Re: Order of operations : negation vs exponentiation Hi, Peter. The proper rule is that negation has the same precedence as multiplication and division, as in the HP. After all, negation means multiplication by -1. So -a^b should be taken as -(a^b). Some time ago when I was researching order of operations for another inquiry, I ran across this page from Microsoft explaining this quirk: XL: Order of Precedence Causes Unexpected Positive Value http://support.microsoft.com/support/kb/articles/Q132/6/86.asp It doesn't claim that their rule agrees with the mathematical world, only that they make their own standards, and don't have to agree with anyone: This behavior is by design of Microsoft Excel. Microsoft Excel uses an order of calculation to evaluate operators in formulas. The order of evaluation of operators dictates that a minus sign (-) used as a negation operator (such as -1) is evaluated before all other operators. Because of this order, the formula =-1^2 represents the value -1 squared, and returns the value 1, a positive value...Note that this has been the standard method for evaluating formulas since the first version of Microsoft Excel. NOTE: This order of operation is different from the order of operation in Lotus 1-2-3. What I suspect is that the programmers were accustomed to the C language, in which unary operators such as negation have higher precedence than any binary operator; there is no exponent operator in C. When they added the exponent operator, they may simply have forgotten that it should have higher precedence, or they may have found it was easier to program this way. Once the rule became established, they couldn't change it and make customer's programs fail. There are many other instances where calculators or software make their own rules; sometimes this is because of limitations of their interface, sometimes because of a misguided desire to "improve" the rules. In any case, we can't use any piece of software as our guide to mathematical practice, and students should be taught not to confuse the rules of a particular program with those of math. We wouldn't want Microsoft to be making these decisions for us anyway, would we? - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Date: 12/16/2005 at 15:24:16 From: Erik Subject: Re: Order of Operations and Negation in Excel I have some reactions to your comments about Excel. Fundamentally, Excel is a programming environment, albeit a very high level one. Computer science has obviously had to develop new conventions for representing mathematical formulas, as the pre-computer conventions mostly don't lend themselves to being typed in as characters on a keyboard. As such I think it is highly appropriate that Excel follows these conventions as well. One universal principle in these conventions (at least across the many programming languages I've used as a long-time career programmer) is that unary operators (operators that take a single operand) take a higher order of precedence than binary operators. You can test this principle, on a Windows system, on VBScript language by typing the text "msgbox -2^2" in notepad and saving it as test.vbs, and then double-clicking on the file. (You will see a message box with a (positive) "4" in it). So I think this isn't bad math on Excel's part, but good computer science. If we were talking about a piece of software that modelled traditional mathmatical notation, it would be a different story of course.
Date: 12/16/2005 at 21:27:58 From: Doctor Peterson Subject: Re: Order of Operations and Negation in Excel Hi, Erik. I've actually said more or less the same thing often, and I even mentioned it in that answer: calculators and computer languages have different needs than mathematicians, so we can't expect them to do things just the way we write them in math. Some people take their rules for math from what calculators do, and I discourage that. On the other hand, a calculator (or a program like Excel) is meant to be a tool, and tools need to be designed to work the way the user will expect. The issue is communication: how can it be designed so that I can most easily tell it what I want it to do? Excel is meant to be used by people, not primarily by programmers (of which I have been one, by the way). Therefore its rules ought to be those its typical user would expect it to use, not those a programmer uses. So if I were designing it, I would not want to follow what C or Basic does, but whatever the typical user would expect, insofar as that can be modeled in a program. Unfortunately, in this instance, it's not clear what a user would expect! One who has been trained in algebraic notation should expect negation to be performed after exponentiation; and I would hope that users of Excel would have at least some such training. I doubt that that is quite true; many probably don't recall this rule, and might well expect negation to be done first. I would much prefer that Excel followed the proper math rule, since that would prevent a lot of confusion--it shouldn't be the most knowledgeable who are most confused by it! Perhaps I shouldn't have criticized the Microsoft people, by guessing at their reasons for this convention; but I do see a general problem in the programming community, that we tend to be nearsighted and forget that our users are not ourselves. We're ingrown: we, as a community, make our own rules (as in the "many programming languages" you mention), and then act as if those rules were universal, and everyone should be expected to know them. (The same is true of terminology: why can't error messages be written in English?) Someone was the first to disregard the existing rule in algebra; maybe everyone else has been just following tradition and can be excused, but someone has to take the blame for inventing a "wrong" rule and making everyone else follow. Now I'm interested in a new question: where did this programming convention originate (so I can know whom to properly blame)? I believe that Fortran (which, since its name means "Formula Translation", is intended to model math) gave negation the same precedence as subtraction, and therefore did exponentiation first. It would be interesting, and not surprising, if Basic is the culprit. In any case, it's true that Excel was written in a context that includes contradictory rules, so they can't take all the blame for the problem. I still say, however, that it _is_ a problem. If you have any further comments, feel free to write back. - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Date: 12/19/2005 at 13:30:33 From: Erik Subject: Re: Order of Operations and Negation in Excel I think you're absolutely right, in that the perrenial challenge for programmers is to put themselves in the shoes of users, so that the front end of the software works the way that is intuitive for them. Further complicating that issue is the fact that it's increasingly universal for students to get some exposure to programming in school, so how will the typical user expect it to work? (I guess that's what focus groups are for.) As to the original "culprit," I think the original Cobol had a strictly left-to-right order of operations. But as to "correctness" or "incorrectness", let me suggest this "numeric philosophy": Negative numbers in fact exist. The number -3 exists. But while our traditional mathematical notation conventions include a distinct symbol for the number 3, it does not do so for the number -3. So, like with most irrational numbers, negative numbers can only be represented as results of operations. The case is entirely different inside your CPU, where 3, and -3 are represented by completely distinct representations, namely 00000000000000000000000000000011, and 11111111111111111111111111111101. And when a programmer types in a -3, he thinks of it (or should) in terms of a distinct number, and not an operation--because when his (compiled) code executes the processor will process it as a distinct number, and never perform any negation operation. (The compiler will perform a negation operation, but that is only to translate from the human notation.) My point being, that unless a -3 can be interpreted as a number, which is to say that the negation takes precidence over all operations between two numbers, then we have no ability to actually represent negative numbers. And since those representations DO exist in the computer, as well as in our minds, we then have a purely language-induced disconnect between how we think about numbers and how the computer processes them.
Date: 12/19/2005 at 14:54:37 From: Doctor Peterson Subject: Re: Order of Operations and Negation in Excel Hi, Erik. And what if there is no typical user? That's the problem here, I guess: some users have no math experience, some have math but not programming, some have programming experience in a language that gives negation high precedence, others in a language that doesn't even have an exponential operator. Life is never easy... >As to the original "culprit," I think the original Cobol had a >strictly left-to-right order of operations. I hadn't thought of Cobol; but since they didn't really follow the order of operations at all, I don't think they can be considered a lineal ancestor of Basic and whatever else has the "wrong" precedence. This would make a very interesting study, though. With regard to your comments on the existence of -3, I had a similar discussion with another programmer recently. The problem with this approach of focusing on -3 as a number, and how it is stored internally (in a computer or a brain), is that we're not talking about internal storage, but about what it looks like on paper or on screen, and that is a sequence of symbols including the negative operator. Whether I am reading it, or a program is reading it, the input process has to take the negative sign as an operator, converting what otherwise would have been a positive number into a negative number. That is, we DON'T represent negative numbers directly, at all; we DO perform a negation operation as we read, though perhaps unconsciously. We could -- there are systems (such as balanced ternary) in which all positive and negative numbers could be represented without a separate negative sign (and also without the limitations imposed by twos complement) -- but we don't use them. We write an operator, so we have to read it as one. The problem is that we humans tend to think in terms of the idea in our heads (a negative number) rather than of what we are actually writing (the negative operator applied to a positive number), and therefore ASSUME that the negative sign is just part of the number and has top precedence. But in an expression like -x^2, where the negative is clearly an operator and not "part of the number -x", we quietly pass over the fact that we are not treating "-" in the same way. It works a lot better if we choose to consistently treat prefix "-" always as an operator with lower precedence than exponentiation, so that -3^2 and -x^2 are treated the same way. On the other hand, since it is so natural to misread the former, a better practice in reality is to avoid writing it, and always parenthesize powers of negative numbers to make it clear what we mean. That's not a bad recommendation to users of Excel or programming languages, too! See this page for more thoughts along these lines: Negative Squared, or Squared Negative? http://mathforum.org/library/drmath/view/61633.html - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Date: 12/20/2005 at 10:44:55 From: Erik Subject: Re: Order of Operations and Negation in Excel Yes, unambiguity is the best practice, so generous parentheses are a good thing. I also agree that in ANY language -3^2 and -x^2 should work the same way, whatever that way is. While it's true in a sense that, as you say, we don't represent negative numbers directly at all, and therefore, writing -3 is necessarily writing an operation, nevertheless a) the mind needs a representation for its conception of "-3", so on at least some level it will persist in thinking of "-3" as being that representation if there's no other alternative representation. b) Treating the negative sign as necessarily an operation is not consistent with how we use our numeral system, e.g. as with -3, we also have no direct representation of 12, so we use "12" as shorthand for 1*10+2. Yet no one would think that 12^2 = 14, because we always think of a number expressed in that notation as a distinct number, rather than the result of the written operations.
Date: 12/20/2005 at 14:02:51 From: Doctor Peterson Subject: Re: Order of Operations and Negation in Excel Hi, Erik. I think you're saying that negation should have higher precedence than exponentiation, even when applied to a variable. But it is a well established convention that, for example, the polynomial -x^2 + 1 means -1*x^2 + 1, not (-x)^2 + 1 = x^2 + 1. You just don't scrap hundreds of years of usage because it doesn't feel right to you. I think the example of the polynomial is one of the main reasons for the rule: we want to be able to read the coefficients of a polynomial easily, and we want to be able to write any polynomial without parentheses. There's just no way we could drop that rule! It may be true that people will always tend to misread -3^2 because the mind WANTS to regard -3 as an atomic (in the sense of "indivisible") representation of the number, and not as an operator; but it is not true that we NEED such a representation, and ought to be indulged. We don't have an atomic representation of a complex number, but we manage to work with them just fine. Not all concepts can, or must be, represented simply. - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Date: 12/20/2005 at 15:05:33 From: Erik Subject: Thank you (Re: Order of Operations and Negation in Excel) I see your point about polynomials. None of the solutions seem very satisfactory to me, short of introducing entirely new notation (like how 'bout ~3 for the number and -3 for the operation?). Thanks for the interesting discussion.
Date: 12/20/2005 at 16:16:38 From: Doctor Peterson Subject: Re: Thank you (Re: Order of Operations and Negation in Excel) Hi, Erik. I've enjoyed it too--the programmer's perspective puts a nice twist on the issue, and makes us think through some deeper ideas. The trouble with ~3 is that we programmer types would want to make "~" an operator and apply it to variables too; it still wouldn't really be atomic, but would end up being a synonym for unary "-", with lower precedence! How about using a red 3 for the negative ;-) - Doctor Peterson, The Math Forum http://mathforum.org/dr.math/
Search the Dr. Math Library:
Ask Dr. MathTM
© 1994-2013 The Math Forum