ExcelFunctions.net

Search Site:

Related Page:

Excel Average FormulaThere 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. ComputersPurchased | |

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:

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

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

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.