This chapter is from the book
This chapter is from the book
This chapter is from the book
Understanding Operator Precedence
You'll often use simple formulas that contain just two values and a single operator. In practice, however, most formulas you use will have a number of values and operators. In these mo s crucial. For example, consider the formula =3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8, and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25, and 3+25 equals 28). As this example shows, a single formula can produce multiple answers, depending on the order in which you perform the calculations.
To control this problem, Excel evaluates a formula according to a predefined order of precedence. This order of precedence enables Excel to calculate a formula unambiguously by determining which part of the formula it calculates first, which part second, and so on.
The Order of Precedence
Excel's order of precedence is determined by the various formula operators outlined earlier. Table 3.4 summarizes the complete order of precedence used by Excel.
Table 3.4 The Excel Order of Precedence
|
Operator |
Operation |
Order of Precedence |
|
: |
Range |
1st |
|
<space> |
Intersection |
2nd |
|
, |
Union |
3rd |
|
-- |
Negation |
4th |
|
% |
Percentage |
5th |
|
^ |
Exponentiation |
6th |
|
* and / |
Multiplication and division |
7th |
|
+ and -- |
Addition and subtraction |
8th |
|
& |
Concatenation |
9th |
|
= < > <= >= <> |
Comparison |
10th |
From this table, you can see that Excel performs exponentiation before addition. Therefore, the correct answer for the formula =3+5^2, given previously, is 28. Notice also that some operators in Table 3.4 have the same order of precedence (for example, multiplication and division). This means that it usually doesn't matter in which order these operators are evaluated. For example, consider the formula =5*10/2. If you perform the multiplication first, the answer you get is 25 (5*10 equals 50, and 50/2 equals 25). If you perform the division first, you also get an answer of 25 (10/2 equals 5, and 5*5 equals 25). By convention, Excel evaluates operators with the same order of precedence from left to right, so you should assume that's how your formulas will be evaluated.
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).
TIP
In 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
)
Figure
3.1 Use parentheses to control the order of precedence in your
formulas.
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:
|
Formula |
1st Step |
2nd Step |
3rd Step |
Result |
|
3^(15/5)*2-5 |
3^3*2–5 |
27*2–5 |
54–5 |
49 |
|
3^((15/5)*2-5) |
3^(3*2–5) |
3^(6–5) |
3^1 |
3 |
|
3^(15/(5*2-5)) |
3^(15/(10-5)) |
3^(15/5) |
3^3 |
27 |
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.