XL: Calculating Weighted Averages

 Last reviewed: February 2, 1998
Article 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
  • Microsoft Excel 98 Macintosh Edition

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 98 XL98
Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.0,3.0,4.0,5.0,98
Platform : MACINTOSH WINDOWS
Issue type : kbhowto kbinfo

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 2, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.