Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
These may be:
1) =Sum(Price)/Count(DISTINCT Month)
2) =Avg(Price)
3) =Sum(Price*Quantity)/Sum(Quantity)
These may be:
1) =Sum(Price)/Count(DISTINCT Month)
2) =Avg(Price)
3) =Sum(Price*Quantity)/Sum(Quantity)