Tabulate Survey Results in Excel 2003

[Stoshu K]

I have a pile of survey results that I'd like to tabulate into one
spreadsheet. Some of the data in the surveys are numerical and some are
text. I’d like to have one master sheet in which I can show an average of
all the numerical responses and show the percentage of respondents that gave
the same answer for that particular question.
Example:
What is the sq. footage of your store? Average store is 1000 sq ft.

What do you look for in a sales rep? 43% = Honesty
22% = Appearance
19% = Accessibility
And so on…

Right now all the returned surveys are in hand-written form on the actual
surveys themselves.
Should I create a worksheet for each respondent with their answers so that
the master sheet can refer back to them to tabulate the averages?
How do I configure the master sheet so that it can keep a running tab and
continue to add new respondents as the surveys are returned to me?
How do you write formulas for these calculations?
Any other advice or questions that will help in understanding what I want to
do?
Thanks so much!!!

 

[Gary''s Student]

No.

I would create one sheet for each question, not each respondent. That will
put all the data needed for statistical analysis on a single sheet.

 

[Stoshu K]

Thanks GS! The advice about creating a new sheet for each question was
helpful. I managed to create some formulas to tabulate the numerical
values...don't know if they were the most efficient way of doing it but they
work. Any thoughts as to how to create statistics for the text and phrase
responses? Example, as below:
Q. What do you look for in a sales rep? 43% answered = Honesty
Any thoughts?