0

My objective is to create a weighted average of price where the weights depend on the value of size per category, using the PowerView functionality in Excel 2013. For instance the following table:

Table

Category  Price    Size
1         10        5
1         20       15
2         30        3
2         50        8
3          5       10
3         33       10

So, for category 1: (10*5+20*15)/20=17.5 and present this in a table:

Category   weighted avg price
 1         17.5
 2        ....

Instead of the unweighted averages which the standard functionality of PowerView produces. Is this possible?

user3071288
  • 1
  • Not sure about PowerView but with standard PivotTables you can create a custom calculation - the quick alternative is to create an additional column which has the weighted value and then run the average over it. – Steph Locke Dec 09 '13 at 11:15

1 Answers1

0

This might not be any new information for you, but you could create two new columns. Column D that is just B x C. And then a column E that is

=sumif($A$1:$A$6,A1,$D$1:$D$6)/sumif($A$1:$A$6,A1,$C$1:$C$6)

and that will give you the weighted average for each category.

turkeyhundt
  • 214
  • 1
  • 15