ExcelFunctions.net

Excel Weighted Average Formula

Related Page:

Excel Average Formula

Home » Excel-Formulas » Excel-Weighted-Average

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


Excel Weighted Average Example

Imagine a company buys:

  • 5 computers at a price of $899
  • 3 computers at a price of $699
  • 2 computers at a price of $949

What is the average price that you paid per computer?

The average price can be calculated using the Excel weighted average formula shown in the spreadsheet on the right. In this spreadsheet, the prices paid for the computers are stored in cells A2 - A4 and the corresponding numbers of purchased computers are shown in cells B2 - B4.

  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 )

The formula to calculate the weighted average, as shown in cell A7, is:

=SUMPRODUCT( A2:A4, B2:B4 ) / SUM( B2:B4 )

The above weighted average formula returns the value 849.00.

I.e. the average price paid for each computer was $849.00.


Further information on calculating a weighted average in Excel can be found on the Microsoft Help and Support website.