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