Calculating weighted average subtotals in a pivot table

**Jack10063** · 10-19-2009, 06:50 AM

Hi,

I have a worksheet as in an attachment. I am using a PT to summarize the data and I'd like to have PRINCIPAL, AVERAGE SPREAD, and WEIGHTED AVERAGE SPREAD as value fields to calculate the subtotals and grand totals. To calculate average of values is straight forward because it can be found from "value field setting" as a function.

However, I don't know how to calculate weighted average of the values. I created a calculated field: WeightedAverage=Sumproduct(PRINCIPAL,SPREAD)/SUM(PRINCIPAL), but that isn't helping..

Any ideas how could I get the weighted average subtotals..?

-Jack

**DonkeyOte** · 10-19-2009, 07:24 AM

Jack, might be an idea to post expected results just so we can be sure we understand the requirements ?

Generally easier to calculate at source.

**Jack10063** · 10-19-2009, 08:21 AM

Thanks for your answer DonkeyOte.. I attached modified file where I have an example column that I would like to see in weighted average column.. So the difference between AVERAGE and WEIGHTED AVERAGE column is that in weighted average the subtotal values are calculated differently.. Is it possible to use customized functions when calculating PT subtotals..?

**DonkeyOte** · 10-19-2009, 09:00 AM

I guess my point was/is conduct calcs @ source, eg

E1: Weighted
E2: =IF(COUNTIF($A$2:$A2,$A2)=1,SUMPRODUCT(--([LEG CODE]=$A2),[PRINCIPAL],[SPREAD])/SUMIF([LEG CODE],$A2,[PRINCIPAL]),0)
the above will auto apply to your table

Then alter your PT source to be =TABLE1, and add the new column in replacement of your calculated item.