Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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


Re: Weighted Average with Pivot Table

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'.


REPOST w/Typo correction...

Posted by Mark W. on December 17, 2001 12:21 PM
...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.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.