Analyzing survey results

[Tina K]

Hi Everyone,

I am analzying the results of a survey for our company and I am wondering if
there is a better (smarter) approach in accomplishing what I want.

SCENARIO: I have an Excel spreadsheet full of data from the results of a
survey. The first row has field names (A1-J1) A1=Group Name, (ie. HR,
IT,), B2= Question 1, C1= Question 2, etc... In the rows just below are
random values, ranging from 1-4. 4= Strongly agree, 3= Somewhat agree,
2=Somewhat disagree, 4= Disagree. I want to calculate the % range of answers
from each dept for each question.

My very very long Solution: I sorted by Group (A1), then ran subtotals to
give me the count at each change of group. Next I wrote a Countif formula to
calculate the count for the first group(I added 4 rows underneath the
subtotal count), then wrote another formula next to this to divide this count
to the subtotal count, then formated the # to %. This works fine, but with
the amount of groups and questions ahead, if I did this manually it could
take some time.

Does anyone have another way to approach this?

Thanks in advance,

 

[Pete_UK]

You can use a SUMPRODUCT formula to give you the equivalent of COUNTIF
but for two conditions (group and score), and then calculate your
percentages from that. You won't have to sort the data or to set up
subtotals. I would suggest doing this on another sheet and setting it
up like this:

Group Score Question1 Question2 Question3 etc
HR 1
HR 2
HR 3
HR 4
IT 1
IT 2

and so on.

Then in C2 you can enter this formula:

=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2))

assuming you have 100 rows of data on Sheet1 - adjust if you have
more. Be careful where you put the $ symbols. The formula can be
copied across and down to give you the counts of the Group and Score
for each question, from which you should be able to derive your
percentages in adjacent columns.

Hope this helps.

Pete

 

[Max]

Does anyone have another way to approach this?

Try pivot table. Eg place Group in ROW area, with Question headers (Q1,Q2,
etc) placed in DATA area (one below the other), and with each set to compute
Averages (or whatever). In the pivot sheet, just drag "Data" and drop it over
"Total" to achieve a columnar view. Tinker to taste.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik

 

[Ashish Mathur]

Hi,

Please read up on pivot tables. Your solution may be as simple and straight
forward as dragging and dropping

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Tina K]

Hi Pete,

Thank you for the suggestion. But, I'm not quite getting the formula to
work. I do not have a "Score" column (yet) as you suggested. Is that a
calculation or the possible survey answers?
My data looks like this:
A1 B1 C1 D1
Group Q1 Q2 Q3
HR 3 3 3
HR 3 4 2
IT 4 3 4
IT 2 2 3

Sincerely,
Tina

 

[Pete_UK]

The score column I suggested is the possible answers that people can
give, so it is just the numbers 1 to 4 in successive cells for HR,
then for IT etc. I assumed that this would occupy column B in the
second sheet. Put the formula in C2 and copy across and down, and then
you will end up with a table like this:

Group Score Question1 Question2 Question3 etc
HR 1 12 7 15
HR 2 18 16 11
HR 3 15 5 17
HR 4 10 27 13
IT 1 9 12 7
IT 2 14 8 13
IT 3
IT 4

and so on, giving you the count for each question by Group and by
Score (or Choice - sorry if the word confused you).

Or, you could try pivot tables, as others have suggested.

Hope this helps.

Pete

 

[Tina K]

Yes, this is very helpful, thanks very much! Thank you to others on the
Pivot table suggestion as well. You guys are great!!!