Weighted Average in Pivot Table

[Ray Kanner]

I have a pivot table based on the following data

Region Team Assets Return
North A 10 12
North B 20 4
North C 30 -5
South A 50 3
South C 25 5
South D 60 8
East A 4 9
East B 12 4
West A 100 6
West B 25 18
West C 3 4
West D 32 1

that looks like this

Average of Return
Region Total
East 6.50
North 3.67
South 5.33
West 7.25
Grand Total 5.75

However, instead of simple average, I would like to see a
weighted average, so for example, the weighted average of
East would be 4/(4+12)* 9 + 12/(4+12)* 4 or 5.50 instead
of the simple average of 6.50. Can I do this within a
pivot table, possibly using calculated fields or items?

Thanks in advance

Ray

 

[Bernie Deitrick]

Ray,

You can't do a weighted average in your pivot table. You will need to
add another column to your source: for your example, assume your data
is in range A113, with the labels in row 1. In Cell E1 enter "Wtd
Average" and in cell E2 use this array formula, entered with
Ctrl-Shift-Enter:

=SUM(IF($A$2:$A$13=A2,$C$2:$C$13*$D$2:$D$13,0))/SUM(IF($A$2:$A$13=A2,$
C$2:$C$13,0))

OR use the regular formula (all on one line)

=SUMPRODUCT(($A$2:$A$13=A2)*1,$C$2:$C$13*$D$2:$D$13)/SUMPRODUCT(($A$2:
$A$13=A2)*1,$C$2:$C$13)

copied down to cell E13. Then do your pivot table, and display the
average of your data field "Wtd Average" in your pivot table.

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

I think I'd do the work on the raw data side--instead of the pivottable.

But first:
4/(4+12)*9 + 12/(4+12)*4
evaluated to 5.25 for me.

If that's what you meant, then this worked ok for me:

Insert a helper column.
Put this in A2 and drag down:
=(C2*D2)/SUMIF($A$2:$A$13,A2,$C$2:$C$13)

Then include it in your pivottable as a Sum.

I got this back:

Sum of total
Region Total
East 5.25
North 0.833333333
South 5.592592593
West 6.8375
Grand Total 18.51342593

 

[Ray Kanner]

Dave, Thanks for your solution. It works well although I
would ideally like one that maintains the integrity of the
weighted average calculation through the totals as well as
through subgroups. For example, in the sample sheet I
presented, the total 'weighted' average is the simple
average of the 4 regions. I realize that this is more
complex but any ideas would be helpful.

PS It evaluated to 5.25 for me too.

Thanks in advance.

Ray

 

[Ray Kanner]

Bernie, Thanks for your solution too and see my response
to Dave. For your solution, in the sample sheet I
presented, the total 'weighted' average is the sum of the
4 regions. Any ideas appreciated.

Thanks in advance.

Ray

 

[Bernie Deitrick]

Ray,

For every level of weighted average that you want, you will need
another column of calcs added to your table, or use complex
conditional formulas.

HTH,
Bernie
MS Excel MVP