Weighted average in a pivot table

[HD]

Is there a way to present a field as a weighted average in
a pivot table?
Consider the following data:
Month Amount Rate
Jan 1000 10%
Jan 7000 6%

In a pivot table, if the the field Rate is presented as an
average, the value for january would be 8% ((10%+6%)/2); I
would like to have the weighted average instead, that is
6.5% ((1000*10%+7000*6%)/(1000+7000))

Thanks.

 

[Debra Dalgleish]

Add a column (Interest) to your source data, in which you calculate the
interest. For example, in cell D2: =B2*C2

Include this column in the pivot table source, and refresh the pivot table.

From the PivotTable menu, choose PivotTable>Formulas>Calculated Field
Type a name for the field, e.g. InterestRate
Enter the formula =Interest/Amount
Click OK

The InterestRate field will be displayed in the data area of the pivot
table