MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Weighted Average with Pivot Table


Posted by John Puckett on December 17, 2001 9:11 AM

Can anyone tell me how to calculate a weighted average within a pivot table. I have the following scenario;
QUALITY
PitNo. Seam Coal Tons ASH SULFUR BTU
1 JBRD 2000 12 0.85 11500
1 JBON 3000 5 0.65 12900
1 JBMK 500 15 1.10 11000
Example for "ASH";
2000 * 12=24000
3000 * 5=15000
500 * 15= 7500
sum [ 5500] sum[46500]

WEIGHTED AVERAGE "ASH" = 46500/5500 = 8.45
For Pit 1 Quality is;
1 8.45 0.76 12218

Posted by Mark W. on December 17, 2001 9:51 AM

Add a new column to your data list, 'Coal Tons*ASH',
that employs the formula, ='Coal Tons'*ASH. After
creating your PivotTable Use the Insert | Calculated
Field... menu command to enter a new, 'Weighted Avg',
field using the formula, ='Coal Tons'*ASH/'Coal Tons'.

Posted by Mark W. on December 17, 2001 12:21 PM

REPOST w/Typo correction...

...After creating your PivotTable Use the Insert |
Calculated Field... menu command to enter a new,
'Weighted Avg', field using the formula,
='Coal Tons*ASH'/'Coal Tons'.

Note: I had misplaced the 2nd apostrophe in the
Calculated Field formula.