Part II: Working with Formulas and Functions
FIGURE 10.1
Calculating the percent of goal
There isn’t much to this formula. You’re simply using cell references to divide one value by
another. You can enter the formula one time in the first row (cell E5 in this case) and then
copy that formula down to every other row in your table.
Alternatively, if you need to compare actuals to a common goal, you can set up a model
like the one shown in Figure 10.2. In this model, each region does not have its own goal.
Instead, you’re comparing the values in the Actual column to a single goal found in cell B3.
=C6/$B$3
FIGURE 10.2
Calculating the percent of goal using a common goal
Note that the cell reference to the common goal is entered as an absolute reference ($B$3).
Using the dollar symbols locks the reference to the goal in place, ensuring that the cell ref-
erence pointing to the common goal does not adjust as you copy the formula down.
See Chapter 9, “Introducing Formulas and Functions,” for more information on absolute and relative
cell references.
