Chapter 4
Section 4.1 – Creating Complex Formulas (Controlling the Order of Operations)
Learning Objectives
- Work with complex formulas by controlling the order of mathematical operations.
- Use the Smart Lookup tool to acquire additional information about percentage calculations.
- Review the use of Absolute cell reference in a division formula.
Download and open FILE: CH 4.1
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 4.1 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:
Table 4.1 – Standard Order of Mathematical Operations |
|
Symbol |
Order |
|
( ) |
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. |
|
^ |
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. |
Using Smart Lookup
Column F requires a Percentage calculation. Before we launch in to creating a calculation for this, it might be handy to know precisely what it is we are looking for. If you are connected to the internet, and are using Excel 2016 or newer, you can use the Smart Lookup tool to get some more information.
- Select cell F2.
- Find the Smart Lookup tool on the Review tab (see Figure 4.1).
- Press the Smart Lookup tool to find more about Percentage calculations.
If this is the first time you have used the Smart Lookup tool, you may need to respond to a statement about your privacy. Press the Got it button.
I think the second article from Microsoft does a pretty good job explaining the calculation, don’t you?
Now that we know what is needed for the Percentage calculation, we can have Excel do the calculation for us. We need to divide the LY Spend for each category by the Annual Spend minus the LY Spend.
- 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 mi