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

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

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

## Applies to:

↑ 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 : 415