Microsoft Excel

Operator Precedence

You can use parentheses in your formulas to control the order in which the calculations occur. As an example, consider the following formula that uses references to named cells.

=Income-Expenses*TaxRate

The goal is to subtract expenses from income and then multiply the result by the tax rate. If you enter the preceding formula, you discover that Excel computes the wrong answer. Rather, 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. Following Table lists Excel's operator precedence. Operations are performed in the order listed in the table. For example, multiplication is performed before subtraction.

Symbol Operator Order of Precedence
Colon (:) Range 1st
A space Intersection 2nd
Comma (,) Union 3rd
- Negation 4th
% Percent 5th
^ Exponentiation 6th
* and / Multiplication and division 7th
+ and - Addition and subtraction 8th
& Text concatenation 9th
=, <, >, <=, >=, and <> Comparison 10th

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 Expense 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.

Nested Parentheses

You can also nest parentheses in formulas-that is, put parentheses inside of parentheses. When a formula contains 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

This formula has four sets of parentheses. Three sets are nested inside the fourth set. Excel evaluates each nested set of parentheses and then sums the three results. This sum is then multiplied by the value in B6.

It's a good idea to make liberal use of parentheses in your formulas even when they aren't necessary. Using parentheses clarifies the order of operations and makes the formula easier to read. For example, if you want to add 1 to the product of two cells, the following formula will do the job:

=A1*A2+1

Because of Excel's operator precedence rules, the multiplication will be performed before the addition. Therefore, parentheses are not necessary. You may find it much clearer, however, to use the following formula even though it contains superfluous parentheses:


=(A1*A2)+1
by BrainBellupdated
Advertisement: