How to calculate weighted averages in Excel

Article ID: 214049 - View products that this article applies to.
This article was previously published under Q214049
For a Microsoft Excel 97 version of this article, see 109211
(http://support.microsoft.com/kb/109211/ )
.

For a Microsoft Excel 98 version of this article, see 192377
(http://support.microsoft.com/kb/192377/ )
.
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.

MORE INFORMATION

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
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top