Weighted average in pivot tables

[Jesse]

Greetings again! I know Pivot Tables can calculate averages, sums, etc., but can they calculate weighted averages?


Below is a table with categories (Level1) and Sub-categories (Level2). Within each sub-category, I want the average (simple enough). For the category (Level1) I want the sub-category averages to be weighted equally.

[pre]

Level1    Level2          Score
Water    Quantity         31
Water    Quantity         23
Water    Variance         28
Water    Variance         52
Water    Variance         82
Health   Knowledge        43
Health   Knowledge        23
Health   Knowledge        63
Health   Practice         37
Health   Attitude         93
Env      ...              ...
...

[/pre]

I've done this in a very round-about way, using several extra columns and larger formulas (see post below - thanks for the help!). Is there a simpler way?


http://chandoo.org/forums/topic/count-unique-values-within-a-dynamic-hierarchal-range

 

[NARAYANK991]

Hi Jesse ,


The posted link does not seem to have what you have described in this post.


Can you explain how you want the weighted average to be calculated ?


For example , if we consider Water in Level1 , there are two counts of Quantity , with values 31 and 23 , and three counts of Variance , with values of 28 , 52 and 82 ; what would the weighted average be ?


Narayan

 

[Jesse]

Hi Narayank991, my apologies. The posted link counts the number of unique Level2's within each Level1, and displays it on each row. I use this in my calculation of the weighted average.


From the data above, example output:

[pre]

Level1    Level2     Average
Water     Quantity   =AVERAGE(31,23) = 27
Water     Variance   =AVERAGE(28,52,82) = 54
Health    Knowledge  =AVERAGE(43,23,63) = 43
Health    Practice   =AVERAGE(37) = 37
Health    Attitude   =AVERAGE(93) = 93

The weighted average for Level1 would then use these averages from Level2 in the calculation, and not just take the average of all items within a Level1 category:


[code]Level1        Weighted Avg
Water         =AVERAGE(Quantity, Variance) = AVERAGE(27,54)
Health        =AVERAGE(Knowledge, Practice, Attitude) =AVERAGE(43,37,93)

[/pre]
In my work, I have 3 categories (Level 1, 2, 3). Currently, on each row in my data table, I have:

- columns to count the occurrences of a Lvl2 in a Lvl1 (prev. ref'd post),

- columns using these counts to get weight,

- and then finally column(i) that multiply Score * Weight(i) for a particular level (i).

- Then, in a Pivot Table, my summary values sum these final columns to get the weighted average scores.


I've tried creating simple calculated fields that use =SUMPRODUCT()[/code] but no success.


Hope this helps. Just wondering if there's a simpler method. I appreciate your input, thanks again

 

[Debraj]

Hi Jesse,


Just a little confusion, How do you like to get the average..

* Is this always in a single column.. or in separate column..

* Or you want to know the Weighted Average according to some input criteria..


I know, my questions are not making sense.. but, if you look at the below download, it may be clear..


https://dl.dropbox.com/u/78831150/Excel/Weighted%20average%20%28Jesse%29.xlsx


BTW, for Average, I have used formula as..

=IF(COUNTIFS($B$1:$B2,$B2,$A$1:$A2,A2)=1,AVERAGE(IF((Level1=$A2)*(Level2=$B2),Score)),"")

Array Formula.. and answer is in E2 to E11,

Then again I used E1 to E11 to fetch Weighted Average I have used formula as..


=IF(COUNTIF($A$1:$A2,$A2)=1,AVERAGE(IF(Level1=$A2,$E$2:$E$11)),"")

Please suggest a better way to display the answer..


Regards,

Deb

 

[Jesse]

Bonjour Deb,


Thanks for responding!


The average I'd like to get will be calculated by the Pivot Table (option "Average" for "Display value as") as opposed to manually. The Level1 and Level2 are not guaranteed to be sorted.


Pivot table averages are by default equally weighted. But when there is more structure to the data (more Levels) I'd like to weight each sub-level equally, based upon the Sublevel averages(Level2 in this case).


To give another example, image we're comparing lunch and dinner prices at restaurant's A and B. We compare the following number of costs from each restaurant

[pre]

Rest A    Rest B
# lunches     4         7
# dinners     6         3

This is entered (unsorted) into a table like this:


Rest (L1)     Meal Type (L2)     Cost
A              Lunch              $x
B              Dinner             $y
...

[/pre]
I want to know the average cost per meal at each restaurant (easy for pivot tables) BUT I also want to know the average cost of lunch, weighting each restaurant equally.


(I just realized that this new example is different from my needs, because here Meal Types can be for either Restaurant, whereas each Level1 has unique Level2's my original example)


I hope this helps, but otherwise I'll just move on because I have something working for now and need to keep working.


Thanks again, talk soon

 

[indesignkat]

I think I'm trying to accomplish the same thing.


I've got a list comprised of account number, total $ of invoices, ADP (average days to pay), sales region, and salesperson. Since the ADP is already an average, if I use "average of" in the pivot table it will return a flat average of the averages, which isn't the correct average for the business as a whole. To calculate that you have to take the ADP for each account, multiply it by that account's total of invoices, sum up all those, then divide by the total of all invoices. This is easily done with sumproduct, but I'm having trouble figuring out how to get a pivot table to use the sumproduct formula instead of its default sum/count/average options.


When using a (non-pivot)table to calculate adp by region/rep, I use:


'=SUMPRODUCT(SUBTOTAL(3,OFFSET(E$2:E22922,ROW(E$2:E22922)-MIN(ROW(E$2:E22922)),,1)),--(E$2:E22922)*(D$2:D22922))/D22923'


where column D has the invoice totals and E has the adp


This looks only at the accounts that are currently visible in the table, i.e. not filtered out, and uses sumproduct to determine the actual adp. I think this would give the weighted average you're looking for, but I don't know how to get a pivot table to use that formula.


Hopefully someone can answer for both of us.


idk