pivot table help

  • Thread starter Thread starter shripaldalal
  • Start date Start date
S

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.
 
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
 
Back
Top