Create "Conditional" Calculated Field in Excel Pivot 1

[NJko4]

Have a MS Excel 2002 pivot table with three row fields; Chg Amt, Paid Amt, and Nbr of Visits and column fields that consist of the 12 months for 2007.

Is it possible to create a calculated field that will divide the value in a particular cell within the pivot table by the respective membership for the month and then multiply by 10,000?

For example, for the month of Jan 2007, the calculated field would be the Nbr of Visits (100) divided by the the membership for Jan 2007 (5000) and then multiply the result by 10000 to arrive at a calculated field amount of 200.

Note, the membership for the month is not currently within the Excel source worksheet.

 

[SkipVought]

Hi,

Can't use reference names and arrays in PivotTable Calculations.

I'd use MS Query instead of a PivotTable, to join the source data and the membership table. faq68-5829.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]