MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot Table help, please

Posted by Stephen S. Wolfe on April 26, 2001 4:29 AM

I have a pivot table I want to build, but I can't get beyond the basic parts of it.

The Excel record looks like the following:

<field layout>
fmpssn Patient Apptdate PCM PCO Team

<sample Data>
20/555-55-5555 Snuffy Smith 2000/04/26 Ayala, Thomas T1
30/000-01-0001 Mary Peck 2000/04/26 Ayala, Thomas T1
01/001-01-1111 Sam Blackley 2000/04/26 Ayala, Thomas T1
20/777-22-3333 Jerry Foster 2000/04/26 Brandt, Jean T2
30/777-22-3333 Pam Foster 2000/04/26 Brandt, Jean T2
20/555-33-1234 Gary Putnam 2000/04/26 Feller, Carl T1
20/666-22-1234 George Tate 2000/04/26 Feller, Carl T1
30/888-11-2345 Mary Leafe 2000/04/26 Feller, Carl T1
20/777-00-1278 Kevin Smith 2000/04/26 Feller, Carl T1
02/888-22-9876 Marsha Jones 2000/04/26 Pinon, Robert T1
30/666-67-3452 Kari Black 2000/04/26 Pinon, Robert T1

Hopefully, the above dataset will serve as a good example.

The first three columns are patient appointment data and the last two columns are PCO (Primary Care Optimization) data with PCM being the Primary Care Manager and Team being the PCO team they are a member of.

What I want to do is create a pivot table that will have as the pivot column the teams and the data portion being the percentile of patients seen that day. This percentile is a percentage of patients divided by the total.

How do I do that?



Posted by Mark W. on April 26, 2001 6:02 AM

Stephen, create a PivotTable with Count of 'fmpssn'
in the DATA area, 'PCO Team' in the ROW area, and
'Apptdate' in the COLUMN area. At step 3 of 4
of the PivotTable Wizard double-click the 'fmpssn'
field label (in the DATA area), press the [Options>>]
button and set the "Show data as" field to
"% of column". T1 saw 81.82% of the patients on
April 26th.