Weighted Average calculation

[Anonymous]

I would like to create a column in a pivot table that shows the weighted average unit price.  What would be the formula for this?

Here is the data:

	Price	Quantity
Jan	-	-
Feb	1.59	15,903.00
Mar	1.65	28,187.00
Apr	1.55	28,330.00
May	1.45	27,755.00
Jun	1.39	28,987.00
Jul	1.44	14,002.00
Aug	1.23	44,112.00
Sep	1.40	28,181.00
Oct	-	-
Nov	-	-
Dec	1.65	100,000.00

Avg Price	1.1133
Avg non Zero	1.4844
Wtd Average	1.5079

What is the formula the weighted average?

[sunny_talwar · Accepted solution]

These may be:

1) =Sum(Price)/Count(DISTINCT Month)

2) =Avg(Price)

3) =Sum(Price*Quantity)/Sum(Quantity)