95

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?

Rodolfo Oviedo
  • 2,077
  • 19
    A negative number squared is a positive number. Which would make the formula 9+3. -(A1)^2 would give you -6. – Ramhound Dec 18 '18 at 19:08
  • 8
    @Ramhound = -(A1)^2 gives 9 in Excel – Rodolfo Oviedo Dec 18 '18 at 19:29
  • 72
    @Ramhound Powers have higher priority than minus signs in any sane environment. – Nobody Dec 18 '18 at 20:35
  • 18
    It ought to be -(A1^2) to get -6... you need parenthesis around the operation, not just the number. Excel is just fine for math, but you need to respect order of operations, and when in doubt, use parenthesis! – SnakeDoc Dec 18 '18 at 21:51
  • 3
    it may be worth noting that the high precedence of the unary - allows you to write e.g. = 3+-1 = 2 – A C Dec 18 '18 at 22:51
  • 14
    This is all about order of operations and nothing to do with Excel. – YetAnotherRandomUser Dec 19 '18 at 13:58
  • 2
    @Kevin a negative sign isn't really an operator, at least how Excel is interpreting it. If OP had written 0 - A1^2 then you get -6. Yes, negation is a thing, but it's not a standard operator. – SnakeDoc Dec 19 '18 at 19:14
  • Can you tell us why (how you obtained) "-6"? That should give you an insight into why Excel naturally gives you "12" rather. – Kris Dec 20 '18 at 08:41
  • 2
    @Kevin No, Excel is respecting the order of operations perfectly fine. Basic math skills are required to write that kind of expression properly ;-) – Skipper Dec 20 '18 at 14:28
  • 2
    LibreOffice Calc, Google Sheets, and Apple's Numbers are all bug-compatible with Excel in this way. – 200_success Dec 22 '18 at 08:46
  • 13
    To all those commenters criticizing the OPs math skills: In standard pure math, this should unambiguously evaluate to -6. In particular, the negative is understood to always mean 0-x (as indicated in the answer). The introduction of a unary - is a topic that is new in applied computer science, and almost always just an implementation detail. Criticizing the OP for not understanding this is like answering a question about confusion with regards to floating point errors with "the program is fine. You just need to check your math. See, if you write these numbers in binary then..." – DreamConspiracy Dec 22 '18 at 12:21
  • 1
    I disagree with @AC about 3+-1. There's no ambiguity there, so precedence resolution isn't necessary. – Ben Millwood Dec 26 '18 at 15:09
  • 1
    @YetAnotherRandomUser: I agree that this is about "order of operations", but it has to do with Excel's order of operations rules. Excel could have been designed with a different set of order of operations rules (actually, I expect that this goes back to decisions made when Lotus 1-2-3 was being designed (Excel was designed to mimic Lotus)) – Flydog57 Dec 26 '18 at 21:36
  • @DreamConspiracy What you are not understanding is that the unary minus carries a higher priority than subtraction. – Loren Pechtel Feb 15 '21 at 22:04

13 Answers13

138

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

= 0-3^2

equals 0-9 = -9.

Why does adding just a 0 change 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 parenthetical expression) that follows. However, the minus sign in 0-3^2 is a subtraction operator, which is a binary operator (with two arguments) 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, Python, mathematically oriented languages (Mathematica, Maple, Fortran, Matlab, Julia), 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. The following is a discussion where a mathematician kills the arguments of a "computer scientist" defending the precedence of negation over exponentiation: Link

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 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 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. Before knowing about the unexpected convention, I once wrote a Gaussian density in Excel as

= EXP(-( (x-mu)/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?

Rodolfo Oviedo
  • 2,077
20

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.)

BruceWayne
  • 2,654
  • 1
    Exactly! Depending on any language or application's precedence rules to be what you think they ought to be is a recipe for trouble. – jamesqf Dec 18 '18 at 17:58
  • 2
    @jamesqf, but there must be some sense and limits to this. Nobody writes 2+(3*4). If a language has arithmetic operations and any precedence rules at all, it absolutely must support all the standard mathematical conventions. There is no excuse for such blunder in Excel. – Zeus Dec 18 '18 at 23:39
  • 5
    @Zeus: Nobody? I probably would, especially if it was in a more complicated expression, or an if-condition. Of course I would write 3 *4 + 2 even if I were leaving out the parens. – jamesqf Dec 19 '18 at 00:57
  • 3
    I long had a suspicion than such a habit of parens overuse comes from the (oh so prevalent) over-exposure to C (and its syntactic descendants). But C is by no means a good example of correct following of the math rules, including precedence (plus it has issues with macros). By contrast, people with initial exposure to more academic systems/languages strongly expect correct design and don't tend to make forward concessions 'just in case'. Hence genuine surprises like in the OP. – Zeus Dec 19 '18 at 02:31
15

The following is Excel's behavior, regardless of its convenience or reasonableness:

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.

=0-3^2 is processed as 0 - 3^2 = -9

And if you have two operators, then the same thing will happen.

=0--3^2 is processed as 0 - (-3)^2 = -9 and =0+-3^2 is processed as 0 + (-3)^2 = 9

Rodolfo Oviedo
  • 2,077
trapper
  • 250
4

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.

routhken
  • 41
  • 4
    “since PEMDAS is not guaranteed in Excel” — No, it is absolutely guaranteed in Excel. Anything else would be madness. Whether or not unary negation (which is distinct from subtraction!) has precedence over exponentiation isn’t covered by PEMDAS. – Konrad Rudolph Dec 19 '18 at 17:02
  • 1
    @routhken Heavy use of parentheses makes writing, editing and debugging cumbersome. To ease those tasks, I check the precedence of operators in the software I am working with and use only the necessary parentheses. In addition, I add spaces to improve readability. – Rodolfo Oviedo Dec 20 '18 at 11:56
  • @KonradRudolph I edited my answer, thank you for the clarification. – routhken Dec 21 '18 at 03:06
  • 1
    I'd prefer using x - x^2. This ensures the - is interpreted as the binary subtraction operator. – Jeter-work Dec 21 '18 at 13:00
  • @KonradRudolph I think the way to look at this is that spreadsheets and computer programming languages use PUEMDAS where unary operations are evaluated after parenthetical operations but before binary mathematical operations. – Jeter-work Dec 21 '18 at 13:02
3

You can have it either way:

=-A1^2+A1

will return a 12, but:

=0-A1^2+A1

will return a -6

If you feel that returning 12 violates common sense; be aware that Google Sheets does the same thing.

Gary's Student
  • 19,396
  • 1
    It seems like the unary minus sign has "too high" precedence. – Andreas Rejbrand Dec 18 '18 at 19:04
  • @AndreasRejbrand It appears to be unitary only if it directly follows the = sign............... =A1-A1^2 also returns -6 – Gary's Student Dec 18 '18 at 19:09
  • 2
    But in the example A1 - A1^2, the minus sign is obviously binary. (A unary operator is one that takes a single operand (like the unary minus sign in -5, or the factorial, the not sign etc.); a binary operator is one that takes two operands (like binary plus, minus, multiplication, union, etc.).) Notice that the minus sign can be unary even if it doesn't follow immediately after the equals sign: 5 + (-4 + 3). – Andreas Rejbrand Dec 18 '18 at 19:15
  • @AndreasRejbrand I agree with you completely! – Gary's Student Dec 18 '18 at 19:16
  • Just to defend Google's reputation, try the search box or bar to input mathematical expressions. You will get very results consistent to good mathematics, even better that from Matlab or Octave, for example, try 2^1^2. – Rodolfo Oviedo Dec 21 '18 at 01:29
  • Your point about google sheets doing that is not particularly relevant; they most likely implemented it to match excel so that excel sheets imported into google sheets will be more likely to work correctly, not because it's the best way of doing things. – alex.forencich Dec 27 '18 at 23:50
  • @Gary'sStudent The minus sign is interpreted as unary operator wherever there is no minuend before the minus sign. Example: =exp(-x^2). Your alternative, =A1-A1^2, is the best. Please upvote the QUESTION if you liked it. – Rodolfo Oviedo Jul 05 '19 at 15:25
3

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.

Paul Smith
  • 147
  • 9
    Of course, Excel could have chosen + to mean multiplication and * to mean subtraction etc. and anyone needing to use Excel would have to know it. But it would have been wrong. The case in point is not the same level of wrong (or silly), but you definitely can argue that Excel defined got the priorities wrong. – Mormegil Dec 20 '18 at 07:37
  • 4
    @Mormegil Well said! Once you try =1+2*2 and see that the answer is 5 and not 6. you are led to assume that Excel follows the rules of algebra. What is the point of misleading people? – Rodolfo Oviedo Dec 20 '18 at 07:56
  • There IS a correct order of precedence, but computers have additional operations. The problem here is that computers use '-' for negation AND for subtraction where the person doing written algebra sees discriminates between negation and subtraction. For the computer to tell the difference it needs a set of rules. In '-x', the '-' is a unary operator (acts on one operand). In '1-x', the '-' is a binary operator. So, Excel (and other computer software) converts -x^2 to (-x)^2. The rest of the order of precedence still applies as we all learned it in grade school. – Jeter-work Dec 21 '18 at 13:10
  • 3
    @Xalorous: Yes, - 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. – wchargin Dec 22 '18 at 10:27
  • @Xalorous What distinction between negation and subtraction does a person see in written algebra? They are visually the same (though the spacing differs conventionally). – Konrad Rudolph Dec 22 '18 at 12:03
  • 1
    @Xalorous the person doing written algebra uses the conventions of their audience combined with parentheses to reduce ambiguity. There is no correct order of precedence, and the rules of algebra are actually just conventions. – Paul Smith Dec 23 '18 at 13:47
3

Alternatively, you could just do

= A1 - A1^2

because -y + x = x-y

lioness99a
  • 131
  • This doesn't explain why that should work, and duplicates numerous earlier answers. – fixer1234 Dec 23 '18 at 21:16
  • @fixer1234 Literally no one else has said that, and I have given the mathematical reason for it? – lioness99a Dec 23 '18 at 23:33
  • Many answers describe turning this into an explicit subtraction. 2. That's not the mathematical reason. The question is about why Excel does not behave that way. The answer is that the negative is not treated as subtraction by Excel.
    • – fixer1234 Dec 24 '18 at 20:04
  • They asked how to prevent the behaviour. I showed them the simplest way. And not a single answer states what I have... – lioness99a Dec 24 '18 at 20:29
  • The best solution. I added to my answer with due credit. If you liked the QUESTION, please upvote it. – Rodolfo Oviedo Jul 05 '19 at 14:32