Sleeper: How to make weighted average in pivot tables

[murfury]

Depart Name Rate Weight
№1 Ann 0.1 200
№1 John 0.2 300
№2 Pit 0.3 400
№2 Poll 0.4 500

I want to obtain pivot table where there is a Rate weighted average on Weight for each Department ?smth like this


Depart Sum Field 1
№1 0.16 % = (0.1*200+0.2*300)/(200+300)%
№2 0.355%
Total 0.286%

Help me plz ((

[Brandtrock]

Are you wanting to do this through code, or by setting up a calculated field manually?

Regards,

[murfury]

Through VBA code, but you understand the task I posted already is simplified.In pivot table menu you can choose sum, average?I want smth like it but with weighted average?any idea will be of great value for me.

[Brandtrock]

Through VBA code, but you understand the task I posted already is simplified.In pivot table menu you can choose sum, average?I want smth like it but with weighted average?any idea will be of great value for me.

In order to get the weighted average in a pivot table you need to use a formula in a calculated item or calculated field. This is pesky enough when doing manually. Having said that, if it can be done manually, it can be done in code too.

I'm about done for the night (3:00 AM local time), but will check back tomorrow and see if any progress has been made on this.

Regards,

[murfury]

I know that I may use fomula like Rate*Weight in pivot table dialog box, but sum in col Weight cannot be normalized to one (i.e. sum(weight)=1) for every group for which weighted average is needed...I donn't know how to resolve it