Forum Discussion
Excel incorrect formula evaluation
Calculating -(1-2)^2 which equals to -1
Wolfram Alpha correct answer: https://www.wolframalpha.com/input/?i=-%281-2%29%5E2
Google correct answer: https://www.google.com/search?q=-%281-2%29%5E2
Even VBA calculates it correctly: Range("A3") = - (1 - 2) ^ 2
But the Excel Function =-(1-2)^2 evaluates to 1 incorrectly.
Please fix it ASAP.
2 Replies
That is correct result. In data modeling (and Excel is data modeling tool) negation which looks like minus has higher priority compare to arithmetic operations
The order in which Excel performs operations in formulas (microsoft.com)
Thus for =-1^2 first negation is applied, after that exponent. Other words
=-1^2 => =(-1)^2 => 1
Same is for Google Sheets
That's differ from calculator result since in the latest there is no negation, only minus.
See also this thread Re: whats wrong with this formula? - Microsoft Tech Community
In Excel, the - operator has higher precedence than the ^ operator, so it evaluates the expression as
-(1-2)^2 = --1^2 = 1^2 = 1.
See Calculation operators and precedence in Excel
We may not agree with this, but we cannot change it, except by adding parentheses:
=-((1-2)^2)
I doubt that Microsoft will be willing to change this, since it would break existing workbooks that depend on this behavior...
