Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Calculating Weighted Averages


View products that this article applies to.

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.

↑ Back to the top


More information

To find a weighted average, follow these steps:
  1. In a new worksheet, enter the following data:
          A1:  Cost     B1:  Cases
          A2:  $.20     B2:  10
          A3:  $.30     B3:  40
    							
  2. 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.

↑ Back to the top


References

"Function Reference," version 4.0, page 424

"User's Guide 1," version 4.0, pages 153-166

"User's Guide," version 3.0 for Windows, pages 275-289

"User's Guide," version 3.0 for the Macintosh, pages 268-281

↑ Back to the top


Keywords: KB109211, kbhowto

↑ Back to the top

Article Info
Article ID : 109211
Revision : 2
Created on : 8/15/2003
Published on : 8/15/2003
Exists online : False
Views : 37