Excel returns wrong value

Question

It took me hours to figure out the root cause of a problem I was having with my spreadsheet.

Let me demonstrate the problem with a simple example. For the formula : =-2^2, excel calculates +4.

According to https://support.office.com/en-us/article/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8 "^" operator is supposed to precede the "-" operator so it should return -4.

This is also the standard math convention taught worldwide

Answer 1

As I said before, your "standard convention" is not the one I learned.

The page indicates that the minus sign indicates both negation (negative value) and subtraction. The assumption I've always worked with is if there is no other number to imply subraction then the negative sign indicates negation.

If the formula was = 4 - 2^2, then yes the sign is subtraction, not negation so the "power" operator is applied before the subtraction.  It would not make sense other wise.

You have 2 options to contact MS. Use them both.

At the bottom of the page you linked to there is a bar "was this information helpful "yes" "no"."  Click on the no and make your comment.

The other way is to submit a feedback to MS

**********************

Send Feedback to MS Developers: Launch Feedback Hub via  <WIN><F>

“Official” MS does not pay much attention to this forum. However, you now can send your complaints and feature requests directly to MS developers and managers using the “Feedback” process described in this link:

https://answers.microsoft.com/en-us/windows/forum/windows_10-windows_install/send-feedback-to-ms-developers/506e7654-8ced-405b-911e-c58d8f253069

Please post a share link to your feedback, back here, so other people who find your question will be able to vote for it and add their comments to it.

************************

Answer 2

The article you cite

https://support.office.com/en-us/article/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8

shows two uses of the hyphen.

The Negation use precedes Exponentiation use. For example, =-2^2 is equivalent to =(-2)^2 and Excel yields 4.

Exponentiation use of the hyphen precedes Subtraction use. For example, =0-2^2 is equivalent to =0-(2^2) and Excel yields -4.

Excel's precedence operations may or may not agree with what one might call "standard math convention," but Excel precedence operations are well documented as Negation before Exponentiation before Subtraction.

When in doubt, enforce your desired preference using parentheses.

Answer 3

Thanks for the quick responses. Standard math convention dictates as follows:

-2^2 = - (2*2) = -4

(-2)^2 = (-2)*(-2) = +4

How do I get Microsoft fix this problem, or issue a clarifying warning? If I knew this problem existed, I could have written my formulas to go around it.

The link I provided in my original inquiry takes you to a page entitled: The order in which Excel performs operations in formulas. It is not a dead link, and is provided as standalone below for your convenience:

https://support.office.com/en-us/article/the-order-in-which-excel-performs-operations-in-formulas-28eaf0d7-7058-4eff-a8ea-0a835fafadb8

 You will need to copy and paste to your browser.

Answer 4

He is saying that according to the order of precedence for operations the formula should be processed as

-(2^2) = -(2*2) = -4   not as (-2)^2 = (-2)*(-2) = +4

I see what he is saying, but I don't ever recall doing his "preferred" way in my math classes. I would also get the positive answer and would expect it to be marked as correct.

I believe the misunderstand comes because he is treating the minus sign as a subtraction, which does happen after the powers operator, instead of as the indicator of the positive/negative value of the number.

Answer 5

Hi Real, the article you linked to is a dead link . . .

Can you please explain this in more detail, as far as I can see Excel is calculating correctly: -2 squared is 4