You are currently offline, waiting for your internet to reconnect

How to calculate weighted averages in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.
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)
 XL2003 XL2007 XL2010 kbhowto formula average weighted
Properties

Article ID: 214049 - Last Review: 09/23/2015 06:43:00 - Revision: 9.0

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

  • kbformula kbhowto kbinfo KB214049
Feedback