Average of % in pivot table

[tc2004]

I must be missing something, but I have been starring at this for a while and
cannot figure out what it is.

I have 5 columns in my table (and 100+ rows):
year
size category
total number of patients
number of patients screened
% of patients screened

I want to create a pivot table that shows the average % of patients screened
by year and size category. If I use average of "% of patients screened" in
the values section of the pivot table the results are off, because it takes
an average of the percentages, which is different from summing the number of
patients and the number screened in each subgroup and then calculating the
percentage.

Is there a way to have a calculation by subgroup in the pivot table:
(sum # patients screened)/(sum # patients)?

Thanks.

 

[Bernie Deitrick]

tc,

You need to add a calculated field - select a cell in the pivot table,
select the "PivotTable" dropdown on the pivot table commandbar, select
"Formulas", then "Calculated Field..." and create a formula like

='Patients screened' /Patients

by selecting the appropriate fields and using the insert button.

For more, see

http://office.microsoft.com/en-us/excel/HP051994821033.aspx

HTH,
Bernie
MS Excel MVP