Adding some kind of calculated field to a pivot table for weighted average

[mick0005]

Hi all -

I have a table of data that I am generating a pivot table off of to sum up some order quantities and prices. I have a calculated field in Column E to total up the CIF cost based on CIF price and volume.

I am hoping to be able to find the WEIGHTED AVERAGE COST of each individual promotion based on the total cost of each Design (qty x cost) divided by the promotion's total order quantity (sum order qty of all designs). Right now if I put in an average field it will just be a straight line average of the cost, not weighted as I have shown I need in Column G.

The data set will increase and the promotion list will grow. I need the pivot table to be dynamic enough so that when the list of promotions grow we can still show the weighted average for each one without having to adjust any formulas, etc..

Is this possible?

Sheet1

*	A	B	C	D	E	F	G
*
Promo 1	Alex	*
*	John	*
*	Joe	*
*	Steve	*
*	Susie	*
*	Rick	*
*	Beth	*
*	Ronald
Promo 2	Don
*	Mike	*
*	Celeste	*
*	David	*
*	Cindy	*
*	Elaine	*
*	Pat	*
*	Drew	*
*
*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:146px;"><col style="width:146px;"><col style="width:184px;"><col style="width:146px;"><col style="width:146px;"><col style="width:146px;"><col style="width:288px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ccccff"]Promotion Name[/TD]
[TD="bgcolor: #ccccff, align: center"]Design Name[/TD]
[TD="bgcolor: #ccccff, align: center"]Sum of FOQ Total Order QTY[/TD]
[TD="bgcolor: #ccccff, align: center"]CIF Price [/TD]
[TD="bgcolor: #ccccff, align: center"]Total Cost[/TD]
[TD="bgcolor: #ccccff, align: center"]Average of CIF Price2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]84270[/TD]
[TD="align: right"]0.452000[/TD]
[TD="align: right"]38090.04[/TD]
[TD="align: right"]0.45200[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]63270[/TD]
[TD="align: right"]0.454000[/TD]
[TD="align: right"]28724.58[/TD]
[TD="align: right"]0.45400[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]73770[/TD]
[TD="align: right"]0.451000[/TD]
[TD="align: right"]33270.27[/TD]
[TD="align: right"]0.45100[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]73770[/TD]
[TD="align: right"]0.456000[/TD]
[TD="align: right"]33639.12[/TD]
[TD="align: right"]0.45600[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]73770[/TD]
[TD="align: right"]0.457000[/TD]
[TD="align: right"]33712.89[/TD]
[TD="align: right"]0.45700[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]73770[/TD]
[TD="align: right"]0.450000[/TD]
[TD="align: right"]33196.5[/TD]
[TD="align: right"]0.45000[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]73770[/TD]
[TD="align: right"]0.453000[/TD]
[TD="align: right"]33417.81[/TD]
[TD="align: right"]0.45300[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]63270[/TD]
[TD="align: right"]0.455000[/TD]
[TD="align: right"]28787.85[/TD]
[TD="align: right"]0.45500[/TD]
[TD="bgcolor: #ffff00"]Correct Weighted Average for each promotion[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #ccffff"]Promo 1 Total[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]579660[/TD]
[TD="bgcolor: #ccffff, align: right"]3.628000[/TD]
[TD="bgcolor: #ccffff, align: right"]2103006.48[/TD]
[TD="bgcolor: #ccffff, align: right"]0.45350[/TD]
[TD="bgcolor: #ffff00, align: center"]0.45344286[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]63270[/TD]
[TD="align: right"]0.524000[/TD]
[TD="align: right"]33153.48[/TD]
[TD="align: right"]0.52400[/TD]
[TD="bgcolor: #ffff00, align: center"]=SUM(E4:F11)/C12[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]55370[/TD]
[TD="align: right"]0.528000[/TD]
[TD="align: right"]29235.36[/TD]
[TD="align: right"]0.52800[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]55370[/TD]
[TD="align: right"]0.523000[/TD]
[TD="align: right"]28958.51[/TD]
[TD="align: right"]0.52300[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]47570[/TD]
[TD="align: right"]0.527000[/TD]
[TD="align: right"]25069.39[/TD]
[TD="align: right"]0.52700[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"]55370[/TD]
[TD="align: right"]0.525000[/TD]
[TD="align: right"]29069.25[/TD]
[TD="align: right"]0.52500[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: right"]47570[/TD]
[TD="align: right"]0.526000[/TD]
[TD="align: right"]25021.82[/TD]
[TD="align: right"]0.52600[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]55370[/TD]
[TD="align: right"]0.522000[/TD]
[TD="align: right"]28903.14[/TD]
[TD="align: right"]0.52200[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]55370[/TD]
[TD="align: right"]0.529000[/TD]
[TD="align: right"]29290.73[/TD]
[TD="align: right"]0.52900[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="bgcolor: #ccffff"]Promo 2 Total[/TD]
[TD="bgcolor: #ccffff"]*[/TD]
[TD="bgcolor: #ccffff, align: right"]435260[/TD]
[TD="bgcolor: #ccffff, align: right"]4.204000[/TD]
[TD="bgcolor: #ccffff, align: right"]1829833.04[/TD]
[TD="bgcolor: #ccffff, align: right"]0.52550[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[paddydive]

Check this -

Excel Weighted Average with Pivot Table

 

[mick0005]

Wonderful! Thanks so much for the quick response. Working great.

Check this -

Excel Weighted Average with Pivot Table