Formulas, Relative References, Order of Operations, and Auditing Formulas
Creating Complex Formulas (Controlling the Order of Operations)
The next formula to be added to the Personal Budget workbook is the percent change over last year. This formula determines the difference between the values in the LY (Last Year) Spend column and shows the difference in terms of a percentage. This requires
that the order of mathematical operations be controlled to get an accurate result.
Table 3 Standard Order of Mathematical Operations shows the standard order of operations for a typical formula. To change the order of operations shown in the table, we use parentheses to process certain mathematical calculations
first. This formula is added to the worksheet as follows:
- Click cell F3 in the Budget Detail worksheet
- Type an equal sign: =
- Type an open parenthesis: (
- Click cell D3. This will add a cell reference to cell D3 to the formula. When building formulas, you can click cell locations instead of typing them
- Type a minus sign: −
- Click cell E3 to add this cell reference to the formula
- Type a closing parenthesis: )
- Type the slash symbol for division: /
- Click cell E3. This completes the formula that will calculate the percent change of last year’s actual spent dollars vs. this year’s budgeted spend dollars (see Figure 6 Adding the Percent Change Formula)
- Press the ENTER key
- Click cell F3 to activate it
- Place the mouse pointer over the Auto Fill Handle
- When the mouse pointer turns from a white block plus sign to a black plus sign, click and drag down to cell F11. This pastes the formula into the range F4:F11
| Symbol | Order |
|---|---|
| ^ | First: Excel executes any exponential computations first. |
| * or / | Second: Excel performs any multiplication or division computations second. When there are multiple instances of these computations in a formula, they are executed in order from left to right. |
| + or − | Third: Excel performs any addition or subtraction computations third. When there are multiple instances of these computations in a formula, they are executed in order from left to right. |
| ( ) | Override Standard Order: Any mathematical computations placed in parentheses are performed first and override the standard order of operations. If there are layers of parentheses used in a formula, Excel computes the innermost parentheses first and the outermost parentheses last. |
Table 3 Standard Order of Mathematical Operations
Figure 6 Adding the Percent Change Formula shows the formula that was added to the Budget Detail worksheet to calculate the percent change in spending. The parentheses were added to this formula to control the
order of operations. Any mathematical computations placed in parentheses are executed first before the standard order of mathematical operations (see Table 3 Standard Order of Mathematical Operations). In this case, if parentheses
were not used, Excel would produce an erroneous result for this worksheet.
Figure 6 Adding the Percent Change Formula
Figure 7 Removing the Parentheses from the Percent Change Formula shows the result of the percent change formula if the parentheses are removed. The formula produces a result of a 299900% increase. Since there is no change between
the LY spend and the budget Annual Spend, the result should be 0%. However, without the parentheses, Excel is following the standard order of operations. This means the value in cell E3 will be divided by E3 first (3,000/3,000), which is 1. Then,
the value of 1 will be subtracted from the value in cell D3 (3,000−1), which is 2,999. Since cell F3 is formatted as a percentage, Excel expresses the output as an increase of 299900%.
Figure 7 Removing the Parentheses from the Percent Change Formula
Integrity Check
Does the Output of Your Formula Make Sense?
It is important to note that the accuracy of the output produced by a formula depends on how it is constructed. Therefore, always check the result of your formula to see whether it makes sense with data in your worksheet. As shown in Figure 7 Removing the Parentheses from the Percent Change Formula, a poorly constructed formula can give you an inaccurate result. In other words, you can see that there is no change between the Annual Spend and LY Spend for Household Utilities. Therefore, the result of the formula should be 0%. However, since the parentheses were removed in this case, the formula is clearly producing an erroneous result.