Thursday, September 23, 2010

Calculate a weighted average

To do this task, use the SUMPRODUCT and SUM functions.
This example calculates the average price paid for a unit across three purchases, where each purchase is for a different number of units at a different price per unit.

A


B


Price per unit


Number of units

1

20


500

2

25


750

3

35


200

 

Formula


Description (Result)

 

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


Divides the total cost of all three orders by the total number of units
ordered (24.66)




This post is brought to you from the microsoft website