Frequency distribution of multiple variables in one table

[felipeplatzer]

Hi all,

I am analyzing a survey where people rank several product attributes on a scale from 1 to 5. I want a frequency distribution (of how many people selected 1,2,3,4,5) for all attributes in one table. Is this possible, preferably with PivotTables? I want to avoid using formulas like COUNTIF, because I want to be able to generate data cuts easily without re-writing the whole formula again.

Thanks a lot!

This is the raw data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product color[/TD]
[TD]Product look and feel[/TD]
[TD]Product price[/TD]
[TD]Product's ease of use[/TD]
[/TR]
[TR]
[TD]Respondent 1[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Respondent 2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Respondent 3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Respondent 4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]


This is the table I need:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product color[/TD]
[TD]Product look and feel[/TD]
[TD]Product price[/TD]
[TD]Product's ease of use[/TD]
[/TR]
[TR]
[TD]% of respondents rating it 1[/TD]
[TD]20%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]70%[/TD]
[/TR]
[TR]
[TD]% of respondents rating it 2[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[/TR]
[TR]
[TD]% of respondents rating it 3[/TD]
[TD]40%[/TD]
[TD]20%[/TD]
[TD]0%[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]% of respondents rating it 4[/TD]
[TD]40%[/TD]
[TD]20%[/TD]
[TD]10%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]% of respondents rating it 5[/TD]
[TD]0%[/TD]
[TD]20%[/TD]
[TD]90%[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

Welcome to the forum.

I can't figure a way to use a PT for this. But it's simpler to use a formula after converting the original data to an official Excel Table.

That way, the Table can have rows added or deleted and all referring formulas will automatically update. You will notice the Results table refers to the data in the Excel Table I called T_Response (which here is A1 to E5) and, accordingly, the formulas use Excel Table Nomenclature. If you are unfamiliar with Excel Tables and their nomenclature, let us know and we'll provide some edification.

I believe your results were not correct for the four respondents you show.

Copy the formula across and down.

Book1
	A	B	C	D	E
1	Respondent	Colour	Apppearence	Price	Usability
2	Respondent 1	1	5	5	3
3	Respondent 2	4	4	5	1
4	Respondent 3	3	2	5	1
5	Respondent 4	4	3	5	1
6
7
8
9	Results
10	Response	Colour	Apppearence	Price	Usability
11	1	25%	0%	0%	75%
12	2	0%	25%	0%	0%
13	3	25%	25%	0%	25%
14	4	50%	25%	0%	0%
15	5	0%	25%	100%	0%
16	Total	100%	100%	100%	100%
Sheet33

Cell Formulas
Range	Formula
B11	=COUNTIFS(T_Response[Colour],$A11)/COUNTA(T_Response[[Respondent]:[Respondent]])