Analyse answers from a questionnaire

[tomhoneyfield]

EDIT 12/03/2010:

- Thanks to this amazing forum, I refined my question for this and posted it elsewhere in another room and someone else Kindly created me an entire section of code that like you recommended, consolidated all the entries into one sheet, where I am using COUNTIF() and SUM() to analyse my results. The pivot table is something I may also attempt depending on time. THANKS AGAIN EVERYONE

Hey,

I'm currently in the process of tearing my noob hair out because I can't for the life of me figure out how to tackle a problem that I need to solve in order to analyse results for my dissertation at university. Basically, I did an online survey, which has 87 respondents, containing questions that mainly have the following answers:
Strongly Agree
Agree
Disagree
Strongly Disagree

Or another example, when asked about relationship status:
Single
In a relationship
Married

Now the survey website provides summary percentages and individual respondent results for free, but wants me to pay them money to cross reference my results, for example, how many people (in a percentage) who chose punk rock, also smoke cigarettes (loose example). I can view each individual response, and I can view a summary of the whole thing, but I can't cross reference without giving them money, and the money they want I don't have (it's bloody expensive), but I've done the questionnaire now so the results are valuable to me.

Right, now first things first, because I can view each individual response on its own, theoretically I have access to all the data, I just need to extract it, record by record.

The easiest way I can imagine to do this, is to copy and paste each full set of questions and answers from each record, one by one onto separate worksheets. So I will have 87 worksheets, with each question and each answer lying in exactly the same cell on each worksheet. I have managed to do this so far with just three records for testing purposes.

The problem I have got now, is I have absolutely no idea how to make the necessary charts to show the percentages that I want. Even a simple summary like, what percentage of respondents chose "Strongly Disagree" to capital punishment, I just have no idea how to make a chart show this, and my noobness might be too great to achieve the task, in which case I shall just give up now, but if anyone has any pointers on where to start it would be much appreciated.

To give you a better perspective, and to show just how hard I'm finding it to analyse even the simplest of results, I'm currently trying to achieve just this:

3 records (each one on a separate worksheet), each with a question asking:

Relationship Status?

For arguments sake let's say the answers they can provide are single, in a relationship or married, so there are three possible results.

So B7 of each record contains the question, and B8 of each record contains the answer.
1 record has a B8 cell that says In a relationship, whilst the other two records have a B8 cell that says Single. I can't even get a pie chart to show 2 thirds Single, 1 third in a relationship, do you get where I'm going?

I'm completely at a loss and I probably haven't even given you anywhere near enough information, and I may not have posted this in the right place but any sort of help would be appreciated thank you

Phew! thanks to anyone who actually read this

[ratcat]

G'day and welcome to the Excel forum,

Does the example in this thread help you ?

http://www.excelforum.com/excel-gene...tionairre.html

Cheers

RC

[tomhoneyfield]

G'day and welcome to the Excel forum,

Does the example in this thread help you ?

http://www.excelforum.com/excel-gene...tionairre.html

Cheers

RC

Thanks for the starter, I shall take a look and let you know

[6StringJazzer]

I have not tried to correlate answers like what you ultimately need to do, but I highly suggest that you not tabulate each respondent on a separate worksheet. It will be much easier to enter and work with the data if you use a single table. Then you might be able to use built-in tools like a pivot table to do some analysis.

I have attached something to give you an idea. You can modify the charts to be pie charts, as you described for the relationship question. (This example also has macros that automatically open a respondent's file to add their answers, but that won't apply to you.)

Correlating answer might be a little trickier, like how many punk rockers smoke. But when your data is set up this way in a single table, it is not such a big leap.

[darkyam]

Edit: Deleted. Walked away from computer for a while and didn't bother to read other responses before I chimed in. I agree wholeheartedly with the pivot table idea.