worksheet function - Why does =-x^2+x for x=3 in Excel result in 12 instead of -6?

Suppose my cell A1 in an Excel spreadsheet holds the number 3. If I enter the formula

= - A1^2 + A1

in A2, then A2 shows the number 12, when it should show -6 (or -9+3)

Why is that? How can I prevent this misleading behaviour?

Answer

Short answer

To solve this problem, just add a 0 before the equal sign

= 0 - A1^2 + A1

or add a couple of parenthesis to force the standard order of operations

= - (A1^2) + A1

or replace the minus sign by its common interpretation of multiplication by -1

= -1 * A1^2 + A1

In this particular case, where you have the extra term +A1, the best solution is that proposed by @lioness99a:

= A1 - A1^2

Detailed explanation

Under Excel's conventions,

= - 3^2

equals (-3)^2 = 9, while

= 0-3^2

equals 0-9 = -9.

Why adding just a 0 changes the result?

Not preceded by a minuend, the minus sign in -3^2 is considered a negation operator, which is a unary operator (with only one argument) that changes the sign of the number (or expression) that follows. However, the minus sign in 0-3^2 is a subtraction operator, which is a binary operator that subtracts what follows - from what precedes -. According to Excel's conventions, the exponentiation operator ^ is computed after the negation operator and before the subtraction operator. See "Calculation operators and precedence in Excel", section "The order in which Excel performs operations in formulas".

The standard mathematical convention is that the exponentiation is computed before both negation and subtraction or, more simply stated, ^ is computed before -. Shamefully, Excel chose different conventions from those of algebra rules, school textbooks, academic writing, scientific calculators, Lotus 1-2-3, Mathematica, Maple, computations oriented languages like Fortran or Matlab, MS Works, and... VBA (the language used to write Excel's macros). Unfortunately, Calc from LibreOffice and Google Sheets follow the same convention for compatibility with Excel. However, placing an expression in Google's search box or bar give excellent results. If you press enter, the order of computations will be given by using parentheses. A discussion where a mathematician kills the arguments of a "computer scientist" defending the precedence of negation over exponenciation: http://mathforum.org/library/drmath/view/69058.html

General Workarounds

If you want to compute

- Anything ^ 2,

add a 0 before the equal sign

0 - Anything ^ 2

or add a couple of parenthesis to force the standard order of operations

- ( Anything ^ 2 )

or replace the minus sign by its common interpretation of multiplication by -1

-1 * Anything ^ 2

Of the alternatives above, I prefer adding a 0 before de minus sign because it is the most practical. If the expression is already surrounded by parentheses, I avoid adding parentheses. Heavy use of parentheses makes expressions harder to read, debug and write.

If an extra term is added (or subtracted without the even-power problem),

- Anything ^ 2 + ExtraTerm,

the best solution is to place the ExtraTerm first,

ExtraTerm - Anything ^ 2.

A comment to another answer says that the only case you have to be aware of the the non-standard precedence rule is where a minus sign follows an equal sign (=-). However, there are other examples, like =exp(-x^2) or =(-2^2=2^2), where there isn't a minuend before the minus sign.

Thanks to @BruceWayne for proposing a short answer, which I wrote at the beginning.

You may be interested in According to Excel, 4^3^2 = (4^3)^2. Is this really the standard mathematical convention?