XL: Calculating Weighted Averages

ID: Q109211

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 2.x, 3.x, 4.x, 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

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

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

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00 4.0 4.00 4.0a 4.00a 5.0 5.00 7.00 8.00 97 XL97

Keywords          : kbdta xlformula 
Version           : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: January 8, 1999