How to calculate weighted averages in Excel

For a Microsoft Excel 97 version of this article, see 109211 .

For a Microsoft Excel 98 version of this article, see 192377 .

Summary

A weighted average differs from an average in that a weighted average returns a number that depends on the variables of both value and weight.

Consider the following example:

A shipment of 10 cases of widgets costs \$0.20 per case. Because of heavy consumption of widgets, a second shipment of 40 cases now costs \$0.30 per case.
The average cost of the cases in each shipment -- determined by the formula (\$0.20+\$0.30)/2 = \$0.25 -- is not an accurate measure of the average cost of the cases because it does not take into account that there are 30 more cases being purchased at \$0.30 than at \$0.20. The weighted average would return \$0.28, a more accurate representation of the average cost of a case of widgets in these two shipments.

To find a weighted average, follow these steps:

1. In a new worksheet, type the following data:
`   A1:  Cost     B1:  Cases   A2:  \$.20     B2:  10   A3:  \$.30     B3:  40 `
2. Type the formula below in any blank cell (it is not necessary to type this formula as an array):
=SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

or

=((A2*B2)+(A3*B3))/SUM(B2:B3)
خصائص

رقم الموضوع: 214049 - آخر مراجعة: 23‏/09‏/2015 - المراجعة: 1

Microsoft Office Excel 2003, Microsoft Office Excel 2007, Microsoft Excel 2010, Excel 2016, Microsoft Excel 2013