Weighted Average Pivot Table Sub Groups

[Cheeseburger]

Hey everyone,

I have a table of data and in it one column has a balance amount in dollars, and another column has an interest rate as a percentage. I have created a pivot table that has various subgroups and I want to have the pivot table calculate the weighted average interest rate by subgroup.

The weight would be based on the dollar figure, so if for example, one subgroup had 2 line items and one item had a balance of $100,000 with an interest rate of 10%, and the other item had a balance of $1,000 and with interest rate of 40%, the weighted average for the subgroup would be 10.297%. It would do this for each of say 10 subgroups.

Any help would be much appreciated.

[PFDave]

Hi Cheeseburger,

I don't think this can be done within the pivot table itself, rather with a formula elsewhere.

Assuming you have these values in 3 columns, Sub Group, Balance, Interest Rate(Value) you could use this formula:

I've assumed that Sub Group is in A, Balance is in B and Interest Rate(Value) is in C

Please Login or Register  to view this content.

You can then attach this formula (tweaked to suit your table) next to the pivot table to display the figures as you require.

As the pivot table will change you can apply an IF statement to the start so your weighted average is only applied once per sub group.

If you need anymore help I'd suggest you upload a sample worksheet with an example of your required output.

Regards

Dave