Frequency distribution of multiple variables in one table

felipeplatzer

New Member
Joined
Sep 15, 2018
Messages
1
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:

Product colorProduct look and feelProduct priceProduct's ease of use
Respondent 11553
Respondent 24451
Respondent 33251
Respondent 44351
...............

<tbody>
</tbody>


This is the table I need:

Product colorProduct look and feelProduct priceProduct's ease of use
% of respondents rating it 120%20%0%70%
% of respondents rating it 20%20%0%20%
% of respondents rating it 340%20%0%10%
% of respondents rating it 440%20%10%0%
% of respondents rating it 50%20%90%0%
Total100%100%100%100%

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,237
Office Version
365
Platform
Windows
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Respondent</td><td style=";">Colour</td><td style=";">Apppearence</td><td style=";">Price</td><td style=";">Usability</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Respondent 1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Respondent 2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Respondent 3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Respondent 4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="background-color: #FFF2CC;;">Results</td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td><td style="text-align: right;background-color: #FFF2CC;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Response</td><td style=";">Colour</td><td style=";">Apppearence</td><td style=";">Price</td><td style=";">Usability</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">75%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">2</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">3</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">4</td><td style="text-align: right;background-color: #E2EFDA;;">50%</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">5</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td><td style="text-align: right;background-color: #E2EFDA;;">25%</td><td style="text-align: right;background-color: #E2EFDA;;">100%</td><td style="text-align: right;background-color: #E2EFDA;;">0%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Total</td><td style="text-align: right;;">100%</td><td style="text-align: right;;">100%</td><td style="text-align: right;;">100%</td><td style="text-align: right;;">100%</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet33</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B11</th><td style="text-align:left">=COUNTIFS(<font color="Blue">T_Response[Colour],$A11</font>)/COUNTA(<font color="Blue">T_Response[[Respondent]:[Respondent]]</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,472
Messages
5,468,823
Members
406,612
Latest member
pedad

This Week's Hot Topics

Top