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.
