Microsoft Office Tutorials and References

In Depth Information

**Formula Problems and Solutions**

Table 21-1:
Operator Precedence in Excel Formulas

Symbol

Operator

Precedence

–

Negation

1

%

Percent

2

^

Exponentiation

3

* and /

Multiplication and division

4

+ and –

Addition and subtraction

5

&

Text concatenation

6

=, <, >, and <>

Comparison

7

When in doubt (or when you simply need to clarify your intentions), use parentheses to ensure

that operations are performed in the correct order. For example, the following formula multiplies

A1 by A2, and then adds 1 to the result. The multiplication is performed first because it has a

higher order of precedence.

=1+A1*A2

The following is a clearer version of this formula. The parentheses aren’t necessary — but in this

case, the order of operations is perfectly obvious.

=1+(A1*A2)

Notice that the negation operator symbol is exactly the same as the subtraction operator symbol.

This, as you may expect, can cause some confusion. Consider these two formulas:

=–3^2

=0–3^2

The first formula, as expected, returns 9. The second formula, however, returns –9. Squaring a

number always produces a positive result, so how is it that Excel can return the –9 result?

In the first formula, the minus sign is a negation operator and has the highest precedence.

However, in the second formula, the minus sign is a subtraction operator, which has a lower

precedence than the exponentiation operator. Therefore, the value 3 is squared, and the result

is subtracted from zero, producing a negative result.

Excel is a bit unusual in interpreting the negation operator. Other spreadsheet products

(for example, Lotus 1-2-3 and Quattro Pro) return –9 for both formulas. In addition,

Excel’s VBA language also returns –9 for these expressions.