Microsoft Office Tutorials and References
In Depth Information
Calculating a Weighted Average
Calculating a Weighted Average
The Excel AVERAGE function returns the average (or mean) of a range of data. Often, users need
to calculate a weighted average. You can search all day, and you won’t find a function to do the
calculation. You can, however, calculate a weighted average by creating a formula that uses the
SUMPRODUCT and the SUM function.
Figure 96-1 shows a simple worksheet that contains 30 days of gasoline prices. For example, the
price was \$2.48 for the first five days. Then it decreased to \$2.41 for two days. The price then
dipped to \$2.39 for three days, and so on.
Figure 96-1: The formula in cell B16 calculates the weighted average of the gas prices.
Cell B15 contains a formula that uses the AVERAGE function:
=AVERAGE(B4:B13)
If you think about it, this formula doesn’t return an accurate result. Rather, the prices must be
weighted by factoring in the number of days that each price was in effect. In other words, a
weighted average is the appropriate type of calculation.
The following formula, in cell B16, does the job:
=SUMPRODUCT(B4:B13,C4:C13)/SUM(C4:C13)
This formula multiplies each price by its corresponding number of days and then adds all those
products. The result is then divided by the number of days. You can easily adapt this formula for
other types of weighted average calculations.

Search JabSto ::

Custom Search