Forum Discussion

Copper Contributor

Dec 14, 2021

Small problem with the Mathematical operation

There is a small problem with a following very simple expression: =-(A2+1)^2 It gives a false positive results, different form this: =-1*(A2+1)^2 Which working correctly. I am using the Offic...

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Dec 14, 2021

JAGGyula

The difference between negation and subtraction relating to Excel is discussing for at least 25 years. Logical negation has function as NOT(). For arithmetic negation it is used "minus" sign in front of expression. Virtually you may consider it something like =NAR(expression) which has high priority instead of =-expression.

For the sample

=-(A2+1)^2 => NAR(A2+1) ^ 2 == (-(A2+1))^2

That's not Excel specific, same is in Google Sheets and any programming language which use minus as arithmetic negation.

JAGGyula

Copper Contributor

Dec 17, 2021

Thank you for the answer. Ok, now I understand, why it is happened. If it is would be in the visual basic, I even can accept, but in the excel I can't, because the mathematics rules are definite and this is not a program language. For example, I won't accept this answer from a student, if he made a same error in a calculation.

SergeiBaklan
Diamond Contributor
Dec 18, 2021
JAGGyula

If consider Excel as calculator with lot of unnecessary features - yes, it shall be school math. If consider Excel as data modelling tool, which is it, it has to have negation and negation works as it works everywhere.

That's not Excel specific, any spreadsheet tool. If you enter

=-5^2

each of Excel, Google Sheets or Zoho Sheet (I believe any other one) return +25. The only difference, some tools as Zoho Sheet automatically converts above formula to

=(-5)^2

which you may see in formula bar.

Again, the key is do we have negation operator or not. School math doesn't introduce it, calculators don't have it. Spreadsheets and programming languages have. As soon as you have such operator you shall accept order of operations with it and don't miss negation with subtraction, even if they have the same minus symbol to indicate the operator.