Pivot Table with percent of main category

PYStraube · Mar 11th, 2002, 04:36 AM

Hello,
I am analyzing response cards from the audience of seminars I conduct.
For one Excel XP pivot table I have, there are three possible responses in the main category of whether the seminar was worth their time(No, Not Sure, and Yes). In the secondary category, there are two possible responses in reference to whether they participate in their company's 401k plan. The third category is the particular seminar they attended.
I would like to know what percent the secondary category is of the main category plus what percentage the third category is of the second category then do the percent of all responses.
Right now, all I can figure out is the percent of all the responses using % of column.
Any help will be greatly appreciated!

Mark W. · Mar 11th, 2002, 07:17 AM

Please provide some sample data.

PYStraube · May 14th, 2002, 05:56 AM

I'd like to upload the spreadsheet to you but am not sure how to do it.

PYStraube · May 24th, 2002, 01:46 PM

How do I upload a small Excel file with some sample data?
Thanks.

Mark W. · May 24th, 2002, 02:02 PM

Quote:

On 2002-05-24 12:46, PYStraube wrote:
How do I upload a small Excel file with some sample data?
Thanks.

Save as CSV and paste into reply posting.

PYStraube · May 25th, 2002, 04:46 AM

Here's some of the data at the bottom.

The layout of the pivot table has "Worth Time" then "Participate" on the "Row" area. Nothing is in the "Column" area. I then have "Count of Worth Time" and "% of Total" in the "Data" area. That gives me a "total" column with the count plus the percent of the overall total. I then created a column to the right of the pivot table that gives me the percent of each "Worth Time" response (Yes, No, Not Sure). So, I'm trying to figure out how to have the pivot table do this column to show the percent of each response. While the responses for this don't change, meaning that I could have the extra column do the calcs accurately each time, I have other types of data I didn't include where the responses would change and thus the formulas for the extra column would no longer be accurate as other response choices are inputted.
Thanks very much for your help!!

WorthTime Participate
No No
No No
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
No Yes
Not Sure No
Not Sure No
Not Sure No
Not Sure No
Not Sure No
Not Sure No
Not Sure No
Not Sure Yes
Not Sure Yes
Not Sure Yes
Not Sure Yes
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes No
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes
Yes Yes

Mark W. · May 27th, 2002, 02:54 PM

Why don't you organize your PivotTable as follows...
*

*	Data	Participate	*	*	*	*
*	Count	*	Pct	*	Total Count	Total Pct
WorthTime	No	Yes	No	Yes	*	*
No	2	15	4%	30%	17	34%
Not Sure	7	4	14%	8%	11	22%
Yes	11	11	22%	22%	22	44%
Grand Total	20	30	40%	60%	50	100%

PYStraube · May 28th, 2002, 07:45 AM

Mark,
Thanks for your time in responding.
Here's a mockup of what I'm trying to do. I couldn't figure how to format it so it you copy and "Paste Special Values" it into Excel, the numbers should go all in the right places. As you can see, I'm looking to do a percentage within each response for "Worth Time" so that, for example, in "Worth Time" "No", the percentage of people who are not participating is 11.76% (2 out of 17) and people who are participating are 88.24% (15 out of 17).
I did find your example quite interesting but I couldn't replicate getting two columns with "yes" and "no" followed by two columns with the percentage of "yes" and "no" followed by a total count and percentage for each "Worth Time" response.
I thought I knew pivot tables pretty well but I guess I don't.
Thanks again for your help!

Worth time? Participating? Data % of Worth Time
No No Worth time 2
% of Gr Tot 4.00% 11.76%
Yes Worth time 15
% of Gr Tot 30.00% 88.24%
No Worth time 17
No % of Total 34.00% 100.00%

Not sure No Worth time 7
% of Gr Tot 14.00% 63.64%
Yes Worth time 4
% of Gr Tot 8.00% 36.36%
No Worth time 11
No % of Total 22.00% 100.00%

Yes No Worth time 11
% of Gr Tot 22.00% 50.00%
Yes Worth time 11
% of Gr Tot 22.00% 50.00%
Yes Worth time 22
Yes % of Total 44.00% 100.00%

Total Worth time 50
Total % of Total 100% 100.00%

Mark W. · May 28th, 2002, 12:37 PM

Previously, you expressed an interest in using '% of total'. Now it appears that '% of row' might be more appropriate...
*

	Data	Participate	*	*	*	*
*	Count	*	% of row	*	Total Count	Total % of row
WorthTime	No	Yes	No	Yes	*	*
No	2	15	11.76%	88.24%	17	100.00%
Not Sure	7	4	63.64%	36.36%	11	100.00%
Yes	11	11	50.00%	50.00%	22	100.00%
Grand Total	20	30	40.00%	60.00%	50	100.00%

This PivotTable is constructed with 2 copies of 'Count of WorthTime' in the DATA area (one configured to Show data as % of row), 'WorthTime' in the ROW area and 'Participate' in the COLUMN area as shown above.

Or, perhaps...
*

*	Participate	Data	*	*	*	*
*	No	*	*	Yes	*	*
WorthTime	Count	% of row	% of total	Count	% of row	% of total
No	2	11.76%	4.00%	15	88.24%	30.00%
Not Sure	7	63.64%	14.00%	4	36.36%	8.00%
Yes	11	50.00%	22.00%	11	50.00%	22.00%
Grand Total	20	40.00%	40.00%	30	60.00%	60.00%

...with 3 copies of 'Count of WorthTime' in the DATA area (one configured to Show data as % of row and one as % of total).

[ This Message was edited by: Mark W. on 2002-05-28 12:01 ]

PYStraube · Nov 10th, 2002, 10:08 PM

Thanks for your latest reply.
When I tried what you kindly suggested, it looking like:
Participate
Worth time Data No Yes Grand Total
No Count 5 20 25
% of Worth time 20.0% 80.0% 100.0%
Not sure Count 20 22 42
% of Worth time 47.6% 52.4% 100.0%
Yes Count 204 429 633
% of Worth time 32.2% 67.8% 100.0%
Total Count 229 471 700
Total % of Worth time 32.7% 67.3% 100.0%

In case you can't make heads or tales of what I just pasted, the "count of worth time" and "% of worth time" are in separate rows. your first table from your 5/28/02 message had the count and percent on the same row.
Would you mind telling me how you did that?
Thanks much!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 04:36 AM	#1
PYStraube New Member Join Date: Mar 2002 Posts: 20	Hello, I am analyzing response cards from the audience of seminars I conduct. For one Excel XP pivot table I have, there are three possible responses in the main category of whether the seminar was worth their time(No, Not Sure, and Yes). In the secondary category, there are two possible responses in reference to whether they participate in their company's 401k plan. The third category is the particular seminar they attended. I would like to know what percent the secondary category is of the main category plus what percentage the third category is of the second category then do the percent of all responses. Right now, all I can figure out is the percent of all the responses using % of column. Any help will be greatly appreciated!

Mar 11th, 2002, 07:17 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Please provide some sample data.

May 14th, 2002, 05:56 AM	#3
PYStraube New Member Join Date: Mar 2002 Posts: 20	I'd like to upload the spreadsheet to you but am not sure how to do it.

May 24th, 2002, 01:46 PM	#4
PYStraube New Member Join Date: Mar 2002 Posts: 20	How do I upload a small Excel file with some sample data? Thanks.

May 25th, 2002, 04:46 AM	#6
PYStraube New Member Join Date: Mar 2002 Posts: 20	Here's some of the data at the bottom. The layout of the pivot table has "Worth Time" then "Participate" on the "Row" area. Nothing is in the "Column" area. I then have "Count of Worth Time" and "% of Total" in the "Data" area. That gives me a "total" column with the count plus the percent of the overall total. I then created a column to the right of the pivot table that gives me the percent of each "Worth Time" response (Yes, No, Not Sure). So, I'm trying to figure out how to have the pivot table do this column to show the percent of each response. While the responses for this don't change, meaning that I could have the extra column do the calcs accurately each time, I have other types of data I didn't include where the responses would change and thus the formulas for the extra column would no longer be accurate as other response choices are inputted. Thanks very much for your help!! WorthTime Participate No No No No No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes Not Sure No Not Sure No Not Sure No Not Sure No Not Sure No Not Sure No Not Sure No Not Sure Yes Not Sure Yes Not Sure Yes Not Sure Yes Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes No Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes

May 28th, 2002, 07:45 AM	#8
PYStraube New Member Join Date: Mar 2002 Posts: 20	Mark, Thanks for your time in responding. Here's a mockup of what I'm trying to do. I couldn't figure how to format it so it you copy and "Paste Special Values" it into Excel, the numbers should go all in the right places. As you can see, I'm looking to do a percentage within each response for "Worth Time" so that, for example, in "Worth Time" "No", the percentage of people who are not participating is 11.76% (2 out of 17) and people who are participating are 88.24% (15 out of 17). I did find your example quite interesting but I couldn't replicate getting two columns with "yes" and "no" followed by two columns with the percentage of "yes" and "no" followed by a total count and percentage for each "Worth Time" response. I thought I knew pivot tables pretty well but I guess I don't. Thanks again for your help! Worth time? Participating? Data % of Worth Time No No Worth time 2 % of Gr Tot 4.00% 11.76% Yes Worth time 15 % of Gr Tot 30.00% 88.24% No Worth time 17 No % of Total 34.00% 100.00% Not sure No Worth time 7 % of Gr Tot 14.00% 63.64% Yes Worth time 4 % of Gr Tot 8.00% 36.36% No Worth time 11 No % of Total 22.00% 100.00% Yes No Worth time 11 % of Gr Tot 22.00% 50.00% Yes Worth time 11 % of Gr Tot 22.00% 50.00% Yes Worth time 22 Yes % of Total 44.00% 100.00% Total Worth time 50 Total % of Total 100% 100.00%

Nov 10th, 2002, 10:08 PM	#10
PYStraube New Member Join Date: Mar 2002 Posts: 20	Thanks for your latest reply. When I tried what you kindly suggested, it looking like: Participate Worth time Data No Yes Grand Total No Count 5 20 25 % of Worth time 20.0% 80.0% 100.0% Not sure Count 20 22 42 % of Worth time 47.6% 52.4% 100.0% Yes Count 204 429 633 % of Worth time 32.2% 67.8% 100.0% Total Count 229 471 700 Total % of Worth time 32.7% 67.3% 100.0% In case you can't make heads or tales of what I just pasted, the "count of worth time" and "% of worth time" are in separate rows. your first table from your 5/28/02 message had the count and percent on the same row. Would you mind telling me how you did that? Thanks much!