Charting Survey Results

  • Thread starter Thread starter Bilbo Baggins
  • Start date Start date
B

Bilbo Baggins

Hi there
I have a set of staff survey results which i am trying to
chart. Each question is in a column, with each
respondent's answers in a separate row. Their answers (A,
B, C or D) to each question then marked in the column
relevant to the question.
I therefore have a list of respondents down the left hand
column, a list of questions along the top row and a mass
of As, Bs, Cs and Ds in the middle.
I am trying to produce a 100% stacked column chart with
individual questions along the x axis and % up the y
axis. Each question will then show % of A answers, % of B
answers, % of C answers and % of D answers.
The responses are starting to come in and i need to find
out how to analyse them!
Any help appreciated.

Bilbo
 
There are three possibilities that come to mind, two that use
PivotTables (one of which requires reorganizing how the data are laid
out) and the third is a 'roll your own.' That's the one documented
below.

Suppose your data are in Sheet1 and start in row 2 (row 1 is a header).
You can use COUNTIF to compute the number of As, Bs, etc. per question.

In another sheet, say Sheet2, in A2:A5 enter the possible responses: A,
B, C, and D. In B1 enter =Sheet1!B1. Copy B1 as far across row 1 as
the number of questions.

With B2 selected -- this is very important, B2 must be selected --
create the named formula (Insert | Name > Define...)
QuestionDataRng =OFFSET(Sheet1!B$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Note that the first address B2 has no $ before the B but it does have
one before the 2. This is very important since it makes the formula
relative to the current column.

Now, in B2 enter the formula =COUNTIF(QuestionDataRng,$A2) This time
note the missing $ before the row number. Copy B2 as far down as there
are responses (row 5 in your case). Copy B2:B5 as far across as there
are questions.

Plot this summary data in a stacked bar. In step 2 of the chart
wizard, ensure that XL knows the data to be 'in rows'.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar - Thanks very much, works a treat.
WIthout wishing to push my luck, could you point me in the
right direction on the pivot tables also as this would
help a great deal with the correlation of different
answers to the survey. I did try this but couldnt get it
to stack the responses properly.
Thanks for your help.

Bilbo
-----Original Message-----
There are three possibilities that come to mind, two that use
PivotTables (one of which requires reorganizing how the data are laid
out) and the third is a 'roll your own.' That's the one documented
below.

Suppose your data are in Sheet1 and start in row 2 (row 1 is a header).
You can use COUNTIF to compute the number of As, Bs, etc. per question.

In another sheet, say Sheet2, in A2:A5 enter the possible responses: A,
B, C, and D. In B1 enter =Sheet1!B1. Copy B1 as far across row 1 as
the number of questions.

With B2 selected -- this is very important, B2 must be selected --
create the named formula (Insert | Name > Define...)
QuestionDataRng =OFFSET(Sheet1!B$2,0,0,COUNTA (Sheet1!$A:$A)-1,1)

Note that the first address B2 has no $ before the B but it does have
one before the 2. This is very important since it makes the formula
relative to the current column.

Now, in B2 enter the formula =COUNTIF
Click to expand...
(QuestionDataRng,$A2) This time
 
You need to set up your data properly to get the most out of a pivot
table. I would set up the data in three columns: Name, Question #, and
Rating. Select the range, then make a Pivot Table (from the Data menu).
Drag Question to the Column area and Rating to the Row area. Then drag
Rating (again) to the Data area, then double click on the Count of
Response button in the Pivot Table, click Options, then choose % of
Column from the Show Data As dropdown. Your pivot table looks like this:

Count of Response Question
Response 1 2 3 4
A 0% 14% 29% 43%
B 43% 14% 14% 43%
C 14% 57% 43% 0%
D 43% 14% 14% 14%

You can make a pivot chart or a regular chart from this data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top