excel - PivotTable - Calculate value depending on combination of row labels -

warning - using excel 2011 macs, inexperienced user

hi all,

i have sheet in excel bunch of categorical fields , numerical ones well. let's looks following:

i make pivot table display average click rate (avg_click_rate) of unique combinations of [year, region], i.e. the combinations of fields in pivottable's rows section.

for example, avg_click_rate of [years=5] is:

(0.5*10)/(10 +5 ) + (0.6*5)/(10+5) = 0.53

while avg_click_rate of [region=north] is:

(0.6*5)/(5+20) + (0.2*20)/(5+20) = 0.28

and avg_click_rate of [years=5, region=south] is:

(0.5*10)/10 = 0.5

i know have make custom calculated field this, life of me cannot figure out how code formula. seriously, appreciated.

to clear, formula be:

sum{ (click_rate * number_members) / sum{number_members} }

where numerator single value each row included in unique combination of [year, region], while denominator constant - total number_members unique combination of [year, region].

you should create new column in source table:

product = click_rate * number_members 

and create calculated field in pivot table:

cf = product / number_members