weighted average on a pivot table

[Marilyn]

Hello out there
I created a pivot table and I need a weighted average . Below is a sample of
my data to create the weighted average.
Jul 06 CA 895 0:00:22
US 11801 0:00:15
Jul 06 Total 12696 0:00:15 (I believe that 0:00:15 is the right answer)

I created a calculated field with the following formula
='ACD + Connect Calls'*'Avg Speed Ans' (which are the names of the two
columns and I get
20.55%
177.64%
472.73%

THe totals do not seem right to me .. what should the formula be? As Is
said before I think the right answer for the weighted average should be
0:00:15

 

[Debra Dalgleish]

In the source data, you could add a column to multiply the calls * avg
speed.
In the pivot table, use that field in the calculated formula, instead of
the Avg Speed Ans field.

 

[Marilyn]

Thanks Debra
THis works, but now a have a different problem, when I add sub totals to
the pivot table , the subtotal is an average of the numbers not a weighted
average

0:00:22 (this is a weighted average )
0:00:15 (this is a weighted average)
sub total 0:00:19 as you can see 0:00:19 is the average subtotal not the
weighted average I want the subtotal to read 0:00:16- which is the weighted
average of :22 and :15 Thank you so much