Part II: Working with Formulas and Functions
Again, note the use of absolute references in the SUM function. This ensures that the SUM
range stays locked as you copy the formula down.
=C3/SUM($C$3:$C$6)
Calculating a running total
Some organizations like to see a running total as a mechanism to analyze the changes in
a metric as a period of time progresses. Figure 10.7 illustrates a running total of units sold
for January through December. The formula used in cell D3 is copied down to each month.
=SUM($C$3:C3)
FIGURE 10.7
Calculating a running total
In this formula, the SUM function is used to add up all of the units from cell C3 to the cur-
rent row. The trick to this formula is the absolute reference ($C$3). Placing an absolute
reference in the reference for the first value of the year locks that value down. This ensures
that as the formula is copied down, the SUM function will always capture and add the units
from the first value to the value on the current row.
Applying a percent increase or decrease to values
A common task for an Excel analyst is to apply a percentage increase or decrease to a given
number. For instance, when applying a price increase to a product, you would typically
raise the original price by a certain percent. When giving a customer a discount, you would
decrease that customer’s rate by a certain percent.
Figure 10.8 illustrates how to apply a percent increase and decrease using a simple formula.
In cell E5, we’re applying a 10% price increase to Product A. In cell E9, we’re giving a 20%
discount to Customer A.
