Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table Calculation (XP, 2003)

    Edited by HansV to present data in table form

    I have a pivot table that analyzes donations. The data has 3 fields; name, department and donation amount.
    A small sample of the data appears below. What I'm trying to do is use a pivot table to get a count of the # of people in each department and % of people who have contributed by department. I used Count as the summary function on the name field to get the # of people in each department and on the Amount field to get the # of contributors. Does anyone know what I can do now to find out the % of people who have contributed (Contributors/# of people in department): Ex: 1 of the 2 people in HR gave, so the answer is 50%.

    <table border=1><td>Name</td><td>Dept</td><td>Amount</td><td>Joe</td><td>PR</td><td align=right>10</td><tr><td>Jack</td><td>PR</td><td align=right>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Calculation (XP, 2003)

    I'm not sure a pivot table is the way to do this. The attached workbook uses SUMPRODUCT formulas.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Calculation (XP, 2003)

    Hans,

    I'm disappointed that this can't be done with a pivot table, but the method you suggested works fine. Thank you very much.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Calculation (XP, 2003)

    > I'm disappointed that this can't be done with a pivot table

    It may be possible, but I'm not very good with calculated fields in pivot tables. Perhaps someone else will come up with a suggestion using pivot tables.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Calculation (XP, 2003)

    YOu could create 2 fields: count of Amount (this will give you those who paid) and the count of the Dept.

    After creating the Pivot table, you could create your own calculation dividing the "count of amount" by "count of Dept" outside the pitovot table and format as percentage.

    Steve

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Calculation (XP, 2003)

    Steve,

    I thought of that, it would seem to make sense but it doesn't work. When I created a simple formula by pointing to the cell containing the value I want to manipulate Excel referenced the cell containing the descriptive label (row field data) & made it absolute. Even after removing the $'s and manually changing the cell reference, when copied the formula gave the same result for each formula. Strange.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table Calculation (XP, 2003)

    Enter the formula manully into the cell instead of by pointing. Just enter the cell reference directly into the cell. It avoids its use of the "getpivotdata" function.

    Steve

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table Calculation (XP, 2003)

    Steve,

    that did it. Thanks,

Posting Permissions

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