Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.
    Attached Files Attached Files
    Jim Whitt
    Pharmacist
    Temple, Texas

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table - counting instances (Excel 2000 SR-1)

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

  3. #3
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table - counting instances (Excel 2000 SR-1)

    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?
    Jim Whitt
    Pharmacist
    Temple, Texas

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table - counting instances (Excel 2000 SR-1)

    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.

  5. #5
    Guy Havers
    Guest

    Re: Pivot table - counting instances (Excel 2000 SR-1)

    One method (file attached): I've added a couple of extra fields to your data, one is a formula for flagging up 'originals', the other simply provides a total. The resulting pivot table is beneath. The percentage column is a calculated field. (I used '97 SR2)

    Hope this helps

    Guy

  6. #6
    Star Lounger
    Join Date
    Dec 2000
    Location
    Temple, Texas, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table - counting instances (Excel 2000 SR-1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •