Excel incorrect formula evaluation

%3CLINGO-SUB%20id%3D%22lingo-sub-3024323%22%20slang%3D%22en-US%22%3EExcel%20incorrect%20formula%20evaluation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3024323%22%20slang%3D%22en-US%22%3E%3CP%3ECalculating%26nbsp%3B-(1-2)%5E2%20which%20equals%20to%20-1%3C%2FP%3E%3CP%3EWolfram%20Alpha%20correct%20answer%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.wolframalpha.com%2Finput%2F%3Fi%3D-%25281-2%2529%255E2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.wolframalpha.com%2Finput%2F%3Fi%3D-%25281-2%2529%255E2%3C%2FA%3E%3C%2FP%3E%3CP%3EGoogle%20correct%20answer%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3D-%25281-2%2529%255E2%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.google.com%2Fsearch%3Fq%3D-%25281-2%2529%255E2%3C%2FA%3E%3C%2FP%3E%3CP%3EEven%20VBA%20calculates%20it%20correctly%3A%20Range(%22A3%22)%20%3D%20-%20(1%20-%202)%20%5E%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20Excel%20Function%26nbsp%3B%3D-(1-2)%5E2%20evaluates%20to%201%20incorrectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20fix%20it%20ASAP.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3024323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3024404%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20incorrect%20formula%20evaluation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3024404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1235471%22%20target%3D%22_blank%22%3E%40dcsaba89blk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20Excel%2C%20the%20-%20operator%20has%20higher%20precedence%20than%20the%20%5E%20operator%2C%20so%20it%20evaluates%20the%20expression%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E-(1-2)%5E2%20%3D%20--1%5E2%20%3D%201%5E2%20%3D%201.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcalculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ECalculation%20operators%20and%20precedence%20in%20Excel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20may%20not%20agree%20with%20this%2C%20but%20we%20cannot%20change%20it%2C%20except%20by%20adding%20parentheses%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D-((1-2)%5E2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20doubt%20that%20Microsoft%20will%20be%20willing%20to%20change%20this%2C%20since%20it%20would%20break%20existing%20workbooks%20that%20depend%20on%20this%20behavior...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

@dcsaba89blk 

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

@dcsaba89blk 

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

image.png

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

image.png

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