Forum Discussion
Squaring with preceding minus
Hello together!
When I put "=-5^2" in a cell, it gives me 25 as a result. Am I doing something wrong? Is that a joke? Is that the standard Excel behaviour?
6 Replies
In addition to above comments. Confusion is since negation and substruction have the same sign "-". Actually negation has higher priority, you may check in Google Sheets or in any other spreadsheet / data modelling software.
In school math we don't separate negation and substruction assuming the latest by default.
Not to beat a dead horse but I thought this was interesting discussion and to further support the discussion you can see in the image below how Excel does treat negation and subtration differently:
First discussion I seen about that is about 25 years ago, but I lost the link. Anyway, that's with any data modelling software, e.g.
Google Sheets
SmartSheets
Rows:
etc.
When I put =-5^2 in a cell, it gives me 25 as a result. [....] Is that the standard Excel behaviour?
It is documented intentional behavior. See "Calculation operators and precedence in Excel".
It is prudent to use parentheses to ensure the intended interpretation: either -(5^2) or (-5)^2. Or we can use the POWER function: -POWER(5, 2) or POWER(-5, 2).
-----
TMI....
Common misunderstanding: Excel's order (unary negation before exponentiation) is "wrong" because it is done differently in math formulas.
That is like arguing that the French are "wrong" because they write "black women" as "femmes noires", putting an adjective after the noun instead of before it. ( And the French would argue that English is "wrong". :wink: )
The point is: Excel and math are different languages, and each has its own rules of syntax.
The only reason why "minus two to the power of four" (which is ambiguous in English) "obviously" seems to be -16 in math is because math has different syntax, namely:
and the rule is: superscript expressions have higher precedence.
Arguably, math has the "nice property" that -2^4 would be equivalent to 0 - 2^4, which is not true in Excel.
OTOH, in math, we can easily write
which is interpreted as 128 (128 = 2^7).
Is Excel "wrong" because we must write 2^(4+3) instead of simply 2^4+3, adding parentheses to force a mathematical interpretation?
( Rhetorical. Of course not! )
Bottom line: Each language has its own syntax and rules for interpretation (semantics). It is only a coincidence -- or by purposeful design -- that some languages share some syntax and semantics. That does not make one or the other language "wrong" when there are differences.
Normally, =-5^2 gives -25 and =(-5)^2 gives 25.
If both give 25, it may be a locale issue, calculation mode, or Excel glitch.
(Go to Formulas → Calculation Options → Automatic).
Use =POWER(-5, 2) or =(-5)*(-5) as a workaround.
Hope this information can help you a little with your plans...if not, please just ignore it.
NikolinoDE , normally =-5^2 gives 25
