inputing survey data

[LadyZ]

Hi,

I have a mission I need help finding a good way to start. I am working with
excel 2003.
What I am working with is:
1) a survey of about 100 sheets
2) each survey having 25 questions one it
3) 4 or 5 possible answers for each question (multiple choice)
4) the surveys are in a set of 4 groups.

what I need to do is have the data from these surveys in a form that can be
entered in for excel to total, average and apply % for the sample or whole
group of any of the questions of the survey.
Now I do not want to have to reinvent the wheel on this so if anyone knows
of the form or template I should use, it would help out a great deal.
If not I was going to put each survey on it’s own page and see if the
program could read the cells form the 100 pages to compile the data.
or is that going about this the hard way.
as you can see I am some what new at this.
Thanks in advance for any suggestions you may have,
LadyZ

 

[Mike Middleton]

LadyZ -

I suggest using a single worksheet with standard database (Excel list)
arrangement and subsequent analysis using pivot tables.

Each row of the database worksheet will have data for a single respondent,
including a unique identifier (ID number), a response for each question (one
column per question, numerical or consistent text entry), and a group number
(if that needs to be analyzed). A row immediately above the data contains
the field name, e.g., ID, Q01,Q02,...,Q25,Group.

Since you seem to have paper results, you are already reinventing the wheel.
You could have used an online questionnaire like Survey Monkey, and you
could use their summaries, or you could download the results in Excel
database format for further analysis.

- Mike
http://www.mikemiddleton.com

 

[Adam]

Hi,

I have a similar problem as LadyZ, I too have made a database in an excel
sheet with the same format as Mike described and it works well to put it in
to a pivot table for all the "normal" questions as there is only one
corresponding answer for every question and ID.

The issue:
When I have a multiple choice question it generates more than one answer per
question and participant ID and I haven't found a way to show this in the
pivot table.

I have tried:

To merge the question cell (horizontally) in the database to use a separate
cell for each answer to that question but it wont allow me to have the merged
cell correspond to multiple underlying cells. I've tried by doing the same
thing by splitting the answers in separate cells vertically and merging the
ID cell to correspond to these cells but it gives the same problem when
making a pivot table.

Hence I need to keep all the answer codes in one cell that can correspond to
the ID and the question. My problem here is that I do not know how to make
one cell represent multiple values in the pivot table.

The bottom right cell in the example below containing the answer codes will
not be presented in the pivot table as one ID that have responded the first
alternative (1) and the second alternative (2) (as I want it to) but as one
answer with code 1,2.

Example:

ID Q1

001 1,2

Please I have spent countless hours with this problem and I would be
extremely happy if someone could help me!!

Help please!!

/Adam