Microsoft KB Archive/214049

= How to calculate weighted averages in Excel 2000 =

Article ID: 214049

Article Last Modified on 9/9/2004

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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.



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



Additional query words: XL2000

Keywords: kbhowto kbinfo kbformula KB214049

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.