- Understanding Formula Basics
- Understanding Operator Precedence
- Controlling Worksheet Calculation
- Copying and Moving Formulas
- Displaying Worksheet Formulas
- Converting a Formula to a Value
- Working with Range Names in Formulas
- Working with Links in Formulas
- Formatting Numbers, Dates, and Times
- From Here
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 more complex expressions, the order in which the calculations are performed becomes 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.5 summarizes the complete order of precedence used by Excel.
Table 3.5 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.5 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, $9.95. In general, the formula is the total cost divided by 1 plus the tax rate, as shown in Figure 3.1.
)
Figure 3.1 The general formula to calculate the pre-tax cost of an item.
Figure 3.2 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.2 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).
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.