XL: Order of Precedence Causes Unexpected Positive Value

 Last reviewed: February 2, 1998
Article ID: Q132686
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition

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 following formula 

   =-2^2
is evaluated as 

   (-2)^2
(that is, with the minus sign evaluated as a negation operator) and returns a positive value, 4.

CAUSE

This behavior occurs by design of Microsoft Excel. 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 (such as =-1^2) applies to the entire formula by inserting parenthesis around the numbers you want to be evaluated first; as in the following example: 

   -(2^2)
This formula returns a negative value, -4.

Note that this has been the standard method for evaluating formulas since the first version of Microsoft Excel.

NOTE: This order of operation is different from the order of operation 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: 

   ARTICLE-ID: Q25189
   TITLE     : 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 in Microsoft Excel, click the Index button in MS Excel Help, type the following text 

   operators, order of precedence formulas
and then double-click the selected text to go to the "The order in which Microsoft Excel performs operations in formulas" help topic.

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

Additional query words: unary minus XL98 XL97 XL7 XL5 XL4 XL3
Keywords : xlformula
Version : WINDOS:2.0,3.0,4.0,5.0,5.0c,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,5.0a,98
Platform : MACINTOSH WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.