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?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communitySuppose 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?
Short answer
To solve this problem, just add a 0 before the equal sign
= 0  A1^2 + A1
or add a couple of parentheses 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
= 03^2
equals 09 = 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 03^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 123, 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 gives 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 exponentiation: 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 parentheses 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 an extra term is added (or subtracted without the evenpower 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 nonstandard 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. Before knowing about the unexpected convention, I once wrote a Gaussian density in Excel as
= EXP(( (xmu)/sigma )^2 / 2 ) / sigma / SQRT( 2*PI() ),
which led to ridiculous results. I solved the problem by adding a 0 before the first 
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 for the order of exponentiation?
A bit more succint than Rodolfo's Answer, you can use:
=(A1^2)+(A1)
(Edit: I totally didn't see it was a self question/answer.)
A leading 
is considered part of the first term.
=3^2
is processed as (3)^2 = 9
With a zero at the start it is instead treated as normal subtraction.
=03^2
is processed as 0  3^2 = 9
And if you have two operators, then the same thing will happen.
=03^2
is processed as 0  (3)^2 = 9
and
=0+3^2
is processed as 0 + (3)^2 = 9
Because Excel is interpreting your equation as:
(x)^2 + x
When you wanted:
(x^2) + x
To prevent this sort of undesired behavior, I find the best practice is to make heavy use of parenthesis to define your own priority system, since negation is not the same as subtraction, and thus not covered by PEMDAS. An example would be like:
((x^2))+x
It might be overkill, but this is how I guarantee Excel behaves the way I want.
x  x^2
. This ensures the  is interpreted as the binary subtraction operator.
Dec 21 '18 at 13:00
The expression =  A1^2 + A1
is specific to Excel so must follow Excels rules. Contrary to some other answers here, there is no correct order of precedence. There are merely different conventions adopted by different applications. For your reference, the order of precedence used by excel is:
: Range
<space> intersection
, union
 Negation
% Percentage
^ Exponential
* and / Multiplication and Division
+ and  Addition and Subtraction
& Concatenation
= < > <= >= <> Comparison
Which you can override using parentheses.

can be unary or binary. But that doesn't imply an order of operations. Other languages get this right: in Python, Ruby, Octave, Awk, and Haskell (the first five languages with an exponentiation operator that came to mind), 3 ** 2
always evaluates to 9
. Why? Because that is the correct answer.
You can have it either way:
=A1^2+A1
will return a 12, but:
=0A1^2+A1
will return a 6
If you feel that returning 12 violates common sense; be aware that Google Sheets does the same thing.
=A1A1^2
also returns 6
Dec 18 '18 at 19:09
Alternatively, you could just do
= A1  A1^2
because y + x = xy
Other people have answered the "how can I avoid this?" part of the question. I am going to tell you why it happens.
It happens because personal computers in 1979 had very limited memory and processing capability.
VisiCalc was introduced for the Apple II in 1979, two years before the initial release of the IBM PC (to which most modern desktop and laptop computers trace their direct ancestry). The Apple II could be had with up to 64 KiB (65,536 bytes) of RAM, and VisiCalc required at least 32 KiB to run. As a bit of an aside here, VisiCalc is rather widely considered to be the "killer application" for the Apple II, and perhaps indeed for personal microcomputers in general.
The fewer special cases and less formula lookahead is required, the simpler (and by consequence smaller) the code to parse a spreadsheet formula can be made. It would therefore make sense to require the user to be somewhat more explicit in corner cases, in exchange for being able to handle larger spreadsheets. Remember, even with a highend Apple II, you only had a few tens of kilobytes to play with after the memory required by the application was accounted for. With a lowmemory system (48 KiB RAM wasn't an uncommon configuration for a "serious" machine), the limit was even lower.
When IBM introduced their PC, a port of VisiCalc to the new architecture was made. Wikipedia refers to this port as "bug compatible", so you'd very much expect to see the exact same formula parsing behavior, even if the system technically was capable of more complex parsing.
Beginning in 1982, Microsoft competed with VisiCalc, and later 123, with their Multiplan crossplatform spreadsheet. Later on, Lotus 123 was introduced in 1983 specifically for the IBM PC, and quickly overtook VisiCalc on it. To make the transition easier, it made sense for both to parse formulas in the same way that VisiCalc did. So the limited lookahead behavior would be carried forward.
In 1985, Microsoft introduced Excel, originally for the Macintosh and beginning with version 2 in 1987 to the PC. Again, to make the transition easier, it made sense to carry forward the formula parsing behavior that people were already used to since by now almost a decade.
With each upgrade of Excel, the opportunity to change the behavior existed, but not only would it require users to learn a new way to type formulas, it would also risk breaking compatibility with spreadsheets used or created with the previous version. In a still very competitive market with several commercial companies competing with each other in each field, the decision was likely made to keep the behavior users were accustomed to.
Fast forward to 2019, and we're still stuck with the formula parsing behavior decisions originally made no later than 19781979.
The expression  A1^2
contains two operators, namely the unary negation operator 
and the binary exponentiation operator ^
. With the absence of any parenthesis, there could be two interpretations. Either:
(A1^2)
or:
(A1)^2
The first one says first do the exponentiation with operands A1
and 2
, and then do the negation on that.
The second one says first do the negation on operand A1
, and then use exponentiation on the result of that and 2
.
As was said in the comments to the question, Powers have higher priority than minus signs in any sane environment. Which means, it is best if a system assumes the first one.
However, Excel prefers the second one.
The lesson is, if you are unsure whether your environments is sane or not, include the parenthesis to be on the safe side. So write (A1^2)
.
This is not a problem with excel but with exponents and negatives. When you take a number and raise it to an even power, you cancel the negative sign.
x^2 + x == (x * x) + x
x = 3 => (3 * 3) + 3
== 9 + 3 => 12
You need to use parenthesis and multiple by 1
1 * (x^2) + x
x^2
where x is 3 and x^2
where x is 3. x^2+x
will never reach 12: wolframalpha.com/input/?i=x%5E2%2Bx
Dec 20 '18 at 7:49
x^2+x where x =3 This is an example of a quadratic equation The equation can be written like this: 3*3+3 :Multiplication takes precedence over addition so result will be written as follows: 9 + 3 :Why =9 because a negative number x a negative number gives a positive result. This can be verified using any calculator, slide rule, or any computer mathematics program Final result 9 + 3 = 12
It is just a really simple maths.
Rule 1. Even multiplications of negative numbers, would output a positive result:
minus * minus = plus
minus * minus * minus = minus
minus * minus * minus * minus = plus
This is due to the fact, that minuses cancel each other in pairs.
Rule 2. The power of every number identifies that this number will be multiplied by itself a number of times.
(2)^n, where n=2 => 2*2 = 4
(2)^n, where n=2 => (2)*(2) = 4
And if you can see Rule number 1..
(3)^n, where n=3 => (3) * (3) * (3) = 9 * (3) = 27
Rule 3. Multiplication and Division have higher priority, than addition and subtraction.
3*5+2 = 15+2 = 17
3*(5+2) = 3*7 = 21
And there is the answer of your question:
Combining all 3 rules from before:
x^2 + x, where x=3 => 3^2+3 = 9+3 = 12
My advice to you is to spend some time every year and keep refreshing the fundamental rules of mathematics.
It is in fact a skill you can maintain and stay on top of a large portion of the world, only by knowing basic maths.
+*/
, but not unary operators like 
or +
. Precedence of the power operator is higher than *
and /
but unary operators have even higher precedence