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/