Creating Complex Formulas
As you saw in the previous section, you can quickly create straightforward formulas with a single operator. But often you'll need to create formulas that use multiple operators, as you also did in the previous section. When you do, you must understand the order in which Excel evaluates the operators so that you can get the calculation correct. You also need to know how to tell Excel to evaluate the operators in a different order. And you may want to break a complex formula into multiple steps so you can see exactly what you're doing.
Understanding the Order in Which Excel Evaluates Operators
In the previous example, you entered the formula =B1-(B2+B4) in cell B5. The parentheses are necessary because the calculation has two separate stages—one stage of subtraction and one stage of addition—and you need to control the order in which they occur.
Try changing the formula in cell B5 to =B1-B2+B4 and see what happens. Follow these steps:
- Click cell B5.
- Click in the Formula bar to start editing the formula there. (You can also edit in the cell by double-clicking the cell or pressing Ctrl+U, but editing in the Formula bar gives you more space, so it's often easier.)
- Delete the opening and closing parentheses.
- Click the Enter button on the Formula bar.
You'll notice that the Net amount (cell B5) jumps substantially. This is because you've changed the meaning of the formula:
- =B1-(B2+B4). This formula means “add the value in cell B2 to the value in cell B4, and then subtract the result from the value in cell B1.”
- =B1-B2+B4. This formula means “subtract the value in cell B2 from the value in cell B1, and then add the value in cell B4 to the result.”
Click cell B5 and press Ctrl+U to open the cell for editing. Position the insertion point before B2 and type (; then position the insertion point after B4 and type ). Then press Return to enter the formula in the cell.
NOTE: As you type a closing parenthesis in a formula, you'll see that the opening parenthesis momentarily darkens to make the pairing clear. When a cell has complex contents and contains other nested items, having the corresponding parenthesis darken like this helps you to identify it.
The order in which Excel evaluates the operators is called operator precedence, and it can make a huge difference in your formulas—so it's vital to know both how it works and how to override it. Table 5–2 shows you the order in which Excel evaluates the operators in formulas.
Table 5–2. Excel's Operator Precedence in Descending Order
| Precedence | Operators | Explanation |
| 1 | – | Negation |
| 2 | % | Percentage |
| 3 | ^ | Exponentiation |
| 4 | * and/ | Multiplication and division |
| 5 | +and– | Addition and subtraction |
| 6 | ' | Concatenation |
| 7 | =, <>, <, <=, >, and>= | Comparison operators |
When two operators are at the same level, Excel performs the operator that appears earlier in the formula first.
Nesting Parts of a Formula to Control Operator Precedence
You can control operator precedence in any formula by nesting one or more parts of the formula in parentheses. For example, as you just saw, using =B1-(B2+B4) makes Excel evaluate B2+B4 before the subtraction.
You can nest parts of the formula several levels deep if necessary. For example, the following formula uses three levels of nesting and returns 180:
=10*(5*(4/(1+1))+8)
Breaking Up a Complex Formula into Separate Steps
A cell can accept more or less as complex a formula as you care to create—and some people enjoy creating formulas that are so complex that it takes an expert to figure them out.
Unless you like to work this way, when you have to create a complex formula, consider breaking it up into separate steps, putting each step in its own cell or row. For example, you could break up that =10*(5*(4/(1+1))+8) formula like this:
- Cell B1. =1+1
- Cell B2. =4/B1
- Cell B3. =5*B2
- Cell B4. =B3+8
- Cell B5. =10*B4
Broken up like this, each formula is easy to read, and you can easily see if any of the steps gives the wrong result. You can type a text description of each step in the next cell for reference, or (more discreetly) insert a comment describing the step.
When you've checked that the formula works, you have the option of creating a new version of the formula that goes into a single cell. But if you want to keep the worksheet easy to read and easy to audit, leave the formula in its step-by-step form.