Warning: Excel Performs Negation Before Exponentiation

What does –5² equal? –25 or 25? If you said –25, then you probably listened in school when you were told that negation should be performed on the same level as subtraction, which comes after exponentiation, so –5²=-(5²)=–25.

If you look up "operator precedence" in Excel's help system, you'll see that Excel performs Negation before Exponentiation, so it evaluates –5² as (–5)².

By the way, in case you were wondering, "negation" is when the minus sign "–" is used as a unary operator, as in -5, which doesn't have an operand on the left side of the expression. Subtraction is a binary operation, as in 6-5, which has an operand on both sides of the "–" operator.

What You Should Do: When using negation, ALWAYS use parentheses to enforce the proper order of operations. Even if YOU know what Excel is doing, don't assume that other people know this quirk about Excel.

Here are some examples of formulas in Excel where a mathematical purist might run into this problem if not using parenthesis around the negated expression.

Excel evaluates as: =(-A1)^2

Excel evaluates as: =5/((-B4)^2)

Excel evaluates as: =5+((-A1)^2+10)

Don't confuse "negation" with "subtraction". The following formula is evaluated like you would expect based on standard order of operations:
Excel evaluates as: =5+A1-(A1^2)

No comments: