XL: Calculating Weighted Averages
ID: Q109211
|
The information in this article applies to:
-
Microsoft Excel for Windows, versions 2.10c, 2.10d, 3.0, 3.0a, 4.0, 4.0a, 5.0, 5.0c
-
Microsoft Excel for the Macintosh, versions 2.20, 2.2a, 3.0, 3.0a, 4.0, 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:
- 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.
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 8.00 97 XL97
Keywords : kbdta xlformula
Version : MACINTOSH:2.20,2.2a,3.0,3.0a,4.0,5.0,5.0a; WINDOWS:2.10c,2.10d,3.0,3.0a,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto
|