Microsoft Office Tutorials and References
In Depth Information
Using Operators in Formulas
Subtraction or negation?
One operator that can cause confusion is the minus sign (–), which you use for subtraction.
However, a minus sign can also be a negation operator, which indicates a negative number.
Consider this formula:
=–3^2
Excel returns the value 9 (not –9). The minus sign serves as a negation operator, and has a
higher precedence than all other operators. The formula is evaluated as “negative 3, squared.”
Using parentheses clarifies it:
=(–3)^2
The formula is not evaluated like this:
=–(3^2)
This is another example of why using parentheses, even if they are not necessary, is a good idea.
Use parentheses to override Excel’s built-in order of precedence. Returning to the previous
example, the formula without parentheses is evaluated using Excel’s standard operator
precedence. Because multiplication has a higher precedence, the Expenses cell multiplies by the
TaxRate cell. Then, this result is subtracted from Income — producing an incorrect calculation.
The correct formula uses parentheses to control the order of operations. Expressions within
parentheses always get evaluated first. In this case, Expenses is subtracted from Income, and the
result multiplies by TaxRate.
Table 2-3: Operator Precedence in Excel Formulas
Symbol
Operator
Colon (:), comma (,), space( )
Reference
Negation
%
Percent
^
Exponentiation
* and /
Multiplication and division
+ and –
Addition and subtraction
&
Text concatenation
=, <, >, <=, >=, and <>
Comparison
 
Search JabSto ::




Custom Search