Microsoft Office Tutorials and References

In Depth Information

This is another example of why using parentheses, even if they are not necessary, is a good idea.

The goal is to subtract expenses from income and then multiply the result by the tax rate. If you enter the pre-

ceding formula, though, you discover that Excel computes the wrong answer. The formula multiplies expenses

by the tax rate and then subtracts the result from the income. In other words, Excel does not necessarily perform

calculations from left to right (as you might expect).

The correct way to write this formula is

=(Income–Expenses)*TaxRate

To understand how this works, you need to be familiar with
operator precedence
— the set of rules that Excel

uses to perform its calculations. Upcoming Table 2-3 lists Excel's operator precedence. Operations are per-

formed in the order listed in the table. For example, multiplication is performed before subtraction.

Use parentheses to override Excel's built-in order of precedence. Returning to the previous example, the for-

mula 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

Nested parentheses

You can also
nest
parentheses in formulas — that is, put parentheses inside parentheses. When a formula con-

tains nested parentheses, Excel evaluates the most deeply nested expressions first and works its way out. The

following example of a formula uses nested parentheses:

=((B2*C2)+(B3*C3)+(B4*C4))*B6