# Thread: Pivot Table Calculation (XP, 2003)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•