I have a situation that requires the need for weight averaged subtotals and
totals. I have no options but to use Pivot Tables for what I am trying to
accomplish. I record coal train data for 25 different contracts and need the
ability to show weight averaged coal qualities in my subtotals by differing
contracts. I have been using pivot tables to track customer information for a
long time and it works perfectly except for the fact that I now need to be
able to weight average some of the information based on the tonnage for each
train which varies significantly. I am somewhat familiar with calculated
fields but the weight average needs to happen at a subtotal based on all of
the coal shipped to individual contracts. Reality would be 25 differing
contracts with 4-5 trains per day to continuously update. All of the
weighting would occur on the tonnage. The list is constantly growing as the
year passes so I need the flexibility of a pivot table vs. hard formulas in a
worksheet. Here is a basic example of what I am trying to do.
Train#1 (15000tons 9500 BTU)
Train#2 (14500tons 9200 BTU)
Train#3 (15150tons 9350 BTU)
Need the weight average BTU
To complicate this matter, the I have no option of changing reports
therefore I need to make this work using pivot tables
Please HELP!!!!!!!!!!

Assume part of your PT with subtotals
looks like this:

Sum of Value Type
Contract Date BTU Weight
A11 1/2/2006 187 299
1/3/2006 96 152
A11 Total 283 451
A13 1/2/2006 92 147
1/4/2006 96 158
A13 Total 188 305
A14 1/1/2006 93 140
1/2/2006 95 144
1/3/2006 92 144
A14 Total 280 428
B11 1/2/2006 96 155
1/3/2006 92 149
1/4/2006 188 299
B11 Total 376 603

When you do
PT toolbar Formulas Calculated Item
you will see no reference to the subtotals,
so you cannot generate your desired formula.
However, if you generate another PT, but this time
leave only Contract in ROW, then you will get
only the subtotals:

Sum of Value Type
Contract BTU Weight Bt/Wt
A11 283 451 0.63
A13 188 305 0.62
A14 280 428 0.65
B11 376 603 0.62

Then add the field Bt/Wt by clicking on Weight, then
Formulas Calculated Item (not Field) Formula:
=BTU/Weight
Name: Bt/Wt or any name you like.

Thank you for the time. I know it was a long question and I believe that your
soultion will be the way to go!

Thanks again

"Herbert Seidenberg" wrote:

Assume part of your PT with subtotals
looks like this:

Sum of Value Type
Contract Date BTU Weight
A11 1/2/2006 187 299
1/3/2006 96 152
A11 Total 283 451
A13 1/2/2006 92 147
1/4/2006 96 158
A13 Total 188 305
A14 1/1/2006 93 140
1/2/2006 95 144
1/3/2006 92 144
A14 Total 280 428
B11 1/2/2006 96 155
1/3/2006 92 149
1/4/2006 188 299
B11 Total 376 603

When you do
PT toolbar Formulas Calculated Item
you will see no reference to the subtotals,
so you cannot generate your desired formula.
However, if you generate another PT, but this time
leave only Contract in ROW, then you will get
only the subtotals:

Sum of Value Type
Contract BTU Weight Bt/Wt
A11 283 451 0.63
A13 188 305 0.62
A14 280 428 0.65
B11 376 603 0.62

Then add the field Bt/Wt by clicking on Weight, then
Formulas Calculated Item (not Field) Formula:
=BTU/Weight
Name: Bt/Wt or any name you like.