XL98: Calculating Weighted Averages (192377)


The information in this article applies to:

  • Microsoft Excel 98 Macintosh Edition
This article was previously published under Q192377

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:
  1. In a new worksheet, enter the following data:

    A1: Cost B1: Cases
    A2: $.20 B2: 10
    A3: $.30 B3: 40

  2. 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)

Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbhowto KB192377
©2002 Microsoft Corporation. All rights reserved.