Many formulas that you create in Excel 2007 perform multiple operations. Excel performs each operation, moving from left to right, in a strict pecking order. This is the natural order of arithmetic operations, sometimes called the order of operations, outlined in the table below. You can use parentheses to change the order of operations, even nesting sets of parentheses within each other.
| Precedence | Operator | Type/Function |
|---|---|---|
| 1 | - | Negation |
| 2 | % | Percent |
| 3 | ^ | Exponentiation |
| 4 | * and / | Multiplication and division |
| 5 | + and - | Addition and subtraction |
| 6 | & | Concatenation |
| 7 | =, <, >, <=, >=, <> | All comparison operators |
Order of operator precedence
Multiplication and division pull more weight than addition and subtraction and, therefore, are performed first, even if these operations don’t come first in the formula (when reading from left to right).
Consider the series of operations in the following formula:
=A2+B2*C2
If cell A2 contains the number 5, B2 contains the number 10, and C2 contains the number 2, Excel evaluates the following formula:
=5+10*2
In this formula, Excel multiplies 10 times 2 to equal 20 and then adds this result to 5 to produce the result 25.
If you want Excel to perform the addition between the values in cells A2 and B2 before the program multiplies the result by the value in cell C2, enclose the addition operation in parentheses as follows:
=(A2+B2)*C2
The parentheses tell Excel that you want this operation performed before the multiplication. If cell A2 contains 5, B2 contains 10, and C2 contains 2, Excel adds 5 and 10 to equal 15 and then multiplies this result by 2 to produce the result 30.
Use nesting in formulas
In fancier formulas, you may need to add more than one set of parentheses, one within another (like the Russian matryoshka dolls that nest within each other) to indicate the order in which you want the calculations to take place. When nesting parentheses, Excel first performs the calculation contained in the most inside pair of parentheses and then uses that result in further calculations as the program works its way outward. For example, consider the following formula:
=(A4+(B4-C4))*D4
Excel first subtracts the value in cell C4 from the value in cell B4, adds the difference to the value in cell A4, and then finally multiplies that sum by the value in D4.
Without the addition of the two sets of nested parentheses, Excel, if left to its own devices, would first multiply the value in cell C4 by that in D4, add the value in A4 to that in B4, and then perform the subtraction.
Don’t worry too much when nesting parentheses in a formula if you don’t pair them properly so that you have a right parenthesis for every left parenthesis in the formula. Excel displays an alert dialog box that suggests the correction that needs to be made to balance the pairs. If you agree with Excel’s suggested correction, you simply click Yes. However, be sure that you only use parentheses: ( ). Excel balks at the use of brackets — [ ] — or braces — { } — in a formula by giving you an Error alert box.