Pivot table – counting instances (Excel 2000 SR-1)

Topic

New Reply

Please see the attached file with sample data, this will make more sense. Thanks. Forgive the long-winded explanation. I’m a pharmacist and work with our VA Medical Center’s Prescription file in preparing reports. Our system allows doctors to enter most of a prescription’s data themselves and a then pharmacist “finishes” them. Or, a pharmacist can enter them “from scratch.” I have been asked to figure out which doctors are complying with management requests to enter their own prescriptions. I’ve designed a report from our central computer listing the original entry person (could be a doctor or a pharmacist), the city where the prescription was entered, and the prescribing doctor. I download that into Excel. What I end up with is a pivot table that counts the number of prescriptions a doctor enters himself, and a second one that counts the number of prescriptions for that doctor (whether he entered them or not). Then I have to do copying and pasting and use VLOOKUP to figure the percentages. Surely there’s an easier way to do this — a calculated field in the pivot tables or something — but I can’t figure it out. Anyone have a suggestion? I’ve checked MSKB but didn’t see anything. Thanks a lot.

Reply | Quote

Replies

If you use the wizard to make your pivot table, then when you see the row, column and data fields (where you have to drag and drop the field names), then after selecting the ‘Count’ for the data field, double-click the datafield, select ‘Options’ in the dialogbox that pops up, and change the ‘Standard’ default into ‘% of total’ or ‘% of rows’ or …

Reply | Quote

Right, Hans. But as I understand it, that method figures percentages within the field. I want to to divide the count of one field by the count of a different field and report that as a percentage. That’s why I created two pivot tables. I can get the job done this way, but it’s very tedious and I am concerned about errors with all those Vlookup formulas. Does that make sense?

Reply | Quote

Jim,

I am puzzled. I really don’t know how to do that with pivot tables. Maybe Andrew or Legare can help here.
I know how to use calculated fields, but here I am stuck. The only thing I got out of it is a ‘division by zero’ error. Sorry! Maybe, you should rearrange your data before you apply a pivot table or use sumif and countif functions, or – and this for sure will work – do it with a macro.

Reply | Quote

Guy, thank you! Yes, it does work. An elegant solution, turning the problem sideways a bit to get the job done. Thanks again.

Reply | Quote