Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 214049 - Last Review: September 18, 2011 - Revision: 6.0
How to calculate weighted averages in Excel
This article was previously published under Q214049
For a Microsoft Excel 97 version of this article, see
109211Â
(http://kbalertz.com/Feedback.aspx?kbNumber=109211/
)
.
For a Microsoft Excel 98 version of this article, see
192377Â
(http://kbalertz.com/Feedback.aspx?kbNumber=192377/
)
.
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:
- In a new worksheet, type the following data:
A1: Cost B1: Cases
A2: $.20 B2: 10
A3: $.30 B3: 40
- 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)
APPLIES TO
- 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 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate