XL2000: Formula Returns Unexpected Positive Value (214279)

SYMPTOMS

In Microsoft Excel, when you use a minus sign (-) as a negation operator (for example, -1) in a formula, the negation operator has higher precedence than a binary operator. This order of precedence may mean that a formula returns a positive value when you expect it to return a negative value. For example, the formula

=-2^2

is evaluated as:

(-2)^2

The minus sign is evaluated as a negation operator. The formula returns a positive value, 4.

CAUSE

Microsoft Excel uses an order of calculation to evaluate operators in formulas. The order of evaluation of operators dictates that a minus sign (-) used as a negation operator (such as -1) is evaluated before all other operators. Because of this order, the formula

=-1^2

represents the value -1 squared, and returns the value 1, a positive value.

WORKAROUND

To preserve the order of operations in a formula, you can specify that the negative symbol in a formula apply to the entire formula by inserting parentheses around the numbers that you want to be evaluated first.

For example, if the earlier formula

=-2^2

is changed to

=-(2^2)

the formula returns a negative value, -4.

This has been the standard method for evaluating formulas since the first version of Microsoft Excel.

NOTE: This order of operations is different from the order of operations in Lotus 1-2-3.

The third-party products that are discussed in this article are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the performance or reliability of these products.

REFERENCES

For additional information about the order of evaluation of operators, click the article number below to view the article in the Microsoft Knowledge Base:

214316 XL2000: Order of Operations Performed in Formulas

For more information about operator precedence, click Microsoft Excel Help on the Help menu, type the order in which Microsoft Excel performs operations in formulas in the Office Assistant or the Answer Wizard, and then click Search to view the topic.