Formula Returns Unexpected Positive Value

Article translations Article translations
Article ID: 132686 - View products that this article applies to.
This article was previously published under Q132686
Expand all | Collapse all

On This Page

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 fomula
=-2^2
is changed to
=-(2^2)
the formula returns a negative value, -4.

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

MORE INFORMATION

For additional information about the order of evaluation of operators, please see the following article in the Microsoft Knowledge Base:
25189 Excel: Order of Operations in Formulas
The third-party products discussed here are manufactured by vendors independent of Microsoft; we make no warranty, implied or otherwise, regarding these products' performance or reliability.

REFERENCES

Microsoft Excel 98 Macintosh Edition

For more information about operator precedence, click Contents And Index on the Help menu (or on the Balloon Help menu if you are using a version of the Macintosh operating system earlier than 8.0), click the Index button in Excel Help, type the following text
operators, formulas
and then click Show Topics. Select the "The order in which Microsoft Excel performs operations in formulas" topic, and click Go To. If you are unable to find the information you need, ask the Office Assistant.

Microsoft Excel 97

For more information about operator precedence in Microsoft Excel, click the Index tab in Microsoft Excel 97 Help, type the following text
operators, evaluation order in formulas
and then double-click the selected text to go to the "The order in which Microsoft Excel performs operations in formulas" topic.

Microsoft Excel 7.0

In Microsoft Excel Help, type the following text
operators, precedence
and then double-click the selected text to go to the "Operator Precedence" topic.

Microsoft Excel 5.0

For more information about "Mathematical Operator Evaluation in Lotus 1.2.3 and Microsoft Excel," click the Search button in Microsoft Excel Help, type the following, and then click Display:
operator

Properties

Article ID: 132686 - Last Review: August 15, 2005 - Revision: 1.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbprb KB132686
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com