Excel Pivot Table / Weighted Averages
I want to build multiple pivot tables and update all pivot tables on a monthly basis.
The pivot table is built showing a summarized metric for several marketing campaigns over time. The pivot table will show the month as a column lable, the campaign as a row label, and the metric as the value. An example of the data would be something like the following:
Camp1: M1, 1000, M2, 850, M3, 2000
Camp2: M1, 1250, M3, 2500, M4 1750
I want to build a weighted average for the pivot table that will easily update when I add M4 to the table. The weighted average will be based on another pivot table that contains the monthly counts for each campaign. That pivot table would be build similarly. Example of that data would be:
Camp1: M1, 50000, M2, 49750, M3, 49675
Camp2: M1, 100000, M2, 98500, M3 96750
I believe the correct weighted average would be:
Weighted Avg M1 = ((1000*50000)+(1250*100000))/sum(50000:100000)
This would be repeated for each month.
My question is how can I calculate this in the pivot table so it will easily update when I add Month 4?