How to calculate weighted averages in Excel

Article translations Article translations
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.

For a Microsoft Excel 98 version of this article, see 192377.
Expand all | Collapse all


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):




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

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from