How to calculate weighted averages in Excel

Article translations
Close

Expand all | Collapse all

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)

Properties

Article ID: 214049 - Last Review: September 18, 2011 - Revision: 6.0
APPLIES TO
• Microsoft Excel 2000 Standard Edition
• Microsoft Office Excel 2003
• Microsoft Office Excel 2007
• Microsoft Excel 2010
• Microsoft Excel 2002 Standard Edition
Keywords:
kbformula kbhowto kbinfo KB214049