Microsoft KB Archive/109211

= Calculating Weighted Averages =

Article ID: 109211

Article Last Modified on 8/15/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q109211





SUMMARY
A weighted average differs from an average in that a weighted average returns a number that depends both on its value and its weight.

Consider the following example:

A shipment of 10 cases of widgets costs $0.20 per case.

Due to 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, ($0.20+$0.30)/2 = $0.25, would not be an accurate measure of the average cost of the cases, since it does not take into account that there are thirty 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.



MORE INFORMATION
To find a weighted average, follow these steps:   In a new worksheet, enter the following data:      A1:  Cost     B1:  Cases A2: $.20     B2:  10 A3: $.30     B3:  40   Follow the appropriate procedure below for your version of Microsoft Excel:

Microsoft Excel Versions 4.0 and Later
Enter the formula below in any blank cell. It is not necessary to enter this formula as an array.

=SUMPRODUCT(A2:A3,B2:B3)/SUM(B2:B3)

Microsoft Excel Versions 2.x and 3.0
Enter the formula below as an array in any blank cell.

=SUM(A2:A3*B2:B3)/SUM(B2:B3)



NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

