Creating 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 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)
- 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. 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. |
Figure 6 shows the formula 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). 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 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, 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.