• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint

### Controlling the Order of Precedence

Sometimes you want to override the order of precedence. For example, suppose that you want to create a formula that calculates the pre-tax cost of an item. If you bought something for \$10.65, including 7% sales tax, and you want to find the cost of the item minus the tax, you use the formula =10.65/1.07, which gives you the correct answer of \$9.95. In general, this is the formula:

Figure 3.1 shows how you might implement such a formula. Cell B5 displays the Total Cost variable, and cell B6 displays the Tax Rate variable. Given these parameters, your first instinct might be to use the formula =B5/1+B6 to calculate the original cost. This formula is shown (as text) in cell E9, and the result is given in cell D9. As you can see, this answer is incorrect. What happened? Well, according to the rules of precedence, Excel performs division before addition, so the value in B5 first is divided by 1 and then is added to the value in B6. To get the correct answer, you must override the order of precedence so that the addition 1+B6 is performed first. You do this by surrounding that part of the formula with parentheses, as shown in cell E10. When this is done, you get the correct answer (cell D10).

##### Figure 3.1. Use parentheses to control the order of precedence in your formulas.

TIP

Figure 3.1. how did I convince Excel to show the formulas in cells E9 and E10 as text? I preceded each formula with an apostrophe, as in this example:

'=B5/1+B6

In general, you can use parentheses to control the order that Excel uses to calculate formulas. Terms inside parentheses are always calculated first; terms outside parentheses are calculated sequentially (according to the order of precedence).

TIP

Another good use for parentheses is raising a number to a fractional power. For example, if you want to take the nth root of a number, you use the following general formula:

=number ^ (1 / n)

For example, to take the cube root of the value in cell A1, use this:

=A1 ^ (1 / 3)

To gain even more control over your formulas, you can place parentheses inside one another; this is called nesting parentheses. Excel always evaluates the innermost set of parentheses first. Here are a few sample formulas:

Formula1st Step2nd Step3rd StepResult
3^(15/5)*2-53^3*2–527*2–554–549
3^((15/5)*2-5)3^(3*2–5)3^(6–5)3^13
3^(15/(5*2-5))3^(15/(10-5))3^(15/5)3^327

Notice that the order of precedence rules also hold within parentheses. For example, in the expression (5*2–5), the term 5*2 is calculated before 5 is subtracted.

Using parentheses to determine the order of calculations enables you to gain full control over your Excel formulas. This way, you can make sure that the answer given by a formula is the one you want.

CAUTION

One of the most common mistakes when using parentheses in formulas is to forget to close a parenthetic term with a right parenthesis. If you do this, Excel generates an error message (and offers a solution to the problem). To make sure that you've closed each parenthetic term, count all the left and right parentheses. If these totals don't match, you know you've left out a parenthesis.

• Create BookmarkCreate Bookmark
• Create Note or TagCreate Note or Tag
• PrintPrint