There is no built-in function that will automatically calculate a weighted average in Excel. However, you can easily create your own Excel weighted average formula, using the Excel Sumproduct and Sum functions. This is illustrated in the example below.
| A | B | C | |
|---|---|---|---|
| 1 | Price | No. Computers Purchased |
|
| 2 | $899.00 | 5 | |
| 3 | $699.00 | 3 | |
| 4 | $949.00 | 2 | |
| 5 | |||
| 6 | Average Price Paid (weighted average): | ||
| 7 | =SUMPRODUCT( A2:A4, B2:B4 ) / SUM( B2:B4 ) | ||
Imagine a company buys:
What is the average price paid per computer?
The average price can be calculated using the Excel weighted average formula shown in the above spreadsheet on the right. This spreadsheet lists the different computer prices in cells A2-A4 and the corresponding numbers of computers purchased in cells B2-B4.
As shown in cell A7 of the spreadsheet, the formula to calculate the weighted average is:
The above weighted average formula returns the value 849.00.
I.e. the average price paid per computer is $849.00.
A video explaining the calculation of a weighted average in Excel is provided on the Microsoft Office Support website.