Analyze Survey Questions

[Michael]

Hi Folks - I need to provide counts and percentages of survey data. If
Question 1 has 5 possible answers (1,2,3,4,5), then i need to count how many
1's, 2's, etc., plus calculate the percentage of the counts to the total
number of answers.

The data looks like this:

Q1 Q2 Q3 ...... etc.
1 2 5
2 3 2
5 1 4

etc.

In the past, I have inserted a blank column between the questions, then used
COUNTIF to count the answers in the Question column, and I have used the
blank column to calculate the percentages. It takes a awhile to setup, so I
was hoping there might be a better way using a pivot table. Any ideas?
Thanks.

Michael

 

[Bernard Liengme]

Looks like a great candidate for a pivot table.
What does "calculate the percentage of the counts to the total number of
answers" mean
Do you want to know what percentage of replies to Q1 gave 1, gave 2, etc....
Again a PT can do this.
If you want to ask how to make a PT please tell us what version of Excel you
are using as the screens a little different from version to version

BUT: I do not see the need to insert columns to use COUNTIF. Why not have
these below the responses or even on another sheet?
best wishes

 

[Michael]

Bernard - Here's what the output has looked like in the past:

Answer Q1 Counts Q1 % Q2 Counts Q2 % Q3 Counts Q3 %
1 5 31.25% 3 18.75% 11 68.75%
2 0 0.00% 1 6.25% 3 18.75%
3 2 12.50% 1 6.25% 2 12.50%
4 1 6.25% 2 12.50% 0 0.00%
5 8 50.00% 9 56.25% 0 0.00%
16 100.00% 16 100.00% 16 100.00%


Based on the way the data was collected, I don't see an easy way to produce
these results (not necessarily in this format) with a pivot table. I am very
familiar with pivot tables. If the data had been entered in the following
format:

Student Question # Answer #
a 1 3
a 2 4
b 1 3
b 2 1

etc.

then a pivot table would have easily solved the problem. Do you see my
dilemma?

Michael

 

[Bernard Liengme]

So you already have counts of how may answered 1, 2,3 ... to each Q?
It looks like what you reproduce below without the % columns?
If so, (A) a PT is still possible AND/OR (B) you could get the % values in
another area of the workbook without adding columns.
Want to send (my private email) a sample file. No confidentiality problem
since I do not know the questions!!!
best wishes