pivot table help

[shripaldalal]

Hi,

I have a pivot table with following fields:

Product, Party, Qty, Rate, Amt

Product, Party are row fields, Qty, Rate, Amt are Data fields

Everything works fine in the pivot table, and Qty, Rate and Amt have
their own subtotal rows.

I just want the Rate to have a weighted average. Lets say

Hydrochloric Acid (Product)
- Party1 - Qty:4 - Rate:100 - Amt:400
- Party2 - Qty:8 - Rate:90 - Amt:720

The subtotals for this row appear as:
Subtotal: Qty:12 - Rate:190 - Amt:1120

Qty:12 is correct because qty is 4 and 8 bottles that is 12

Amt:1120 is correct because 400+720 = 1120

But Rate subtotal is completely wrong. Rate cannot be 190 because
190*12 = 2280 which is a completely wrong amount.

Rate should have a weighted average: that is 1120/12 = 93.33

How can I do this ? Please help.

Thanks,
Shripal.

 

[Bernie Deitrick]

Do not use the Rate from your data table.

Select the Pivot Table, then from the Pivot table commandbar select "Pivot Table" "Formulas"
"Calculated Field..." then give is a name like CalcRate, then create a formula = Amt / Qty
(using the Add functionality) and everything will calculate correctly.

HTH,
Bernie
MS Excel MVP

 

[shripaldalal]

Thanks a million, problem sovled!

Best regards,
Shripal Dalal.