Archive of Mr Excel Message Board
Back to Pivot Tables in Excel archive index
Back to archive home
Weighted Average with Pivot TablePosted 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;
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
WEIGHTED AVERAGE "ASH" = 46500/5500 = 8.45
For Pit 1 Quality is;
1 8.45 0.76 12218
Re: Weighted Average with Pivot TablePosted 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.