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.