Analysing ranked data from questionnaire

[christremblett]

Hi, I joined this forum hoping someone will be able to stop me tearing my hair out!
I have just completed vistor surveys of nature reserves and am looking to do some simple analysis of the results using excel, this is fine until i reach a few of our questions.
We were asking what improvements vistors wanted to see and asked them to rank there answers (on set choices we gave them, such as litter, benches etc), how would i input the ranked answers onto a worksheet so i could then show overall what recieved the highest votes according to rank?
Cheers
Chris

 

[Damon Ostrander]

Hi Chris,

I would suggest you allocate a row to each visitor's survey, and have their responses under column headings such as "Litter", "Benches", etc. This should work fine so long as you don't have more than about 250 questions. So each column would then contain all the visitors' rankings for a particular question. You can then add the values in each column to get the total rankings (votes), and by dividing by the number of visitors surveys the average visitor ranking for each question. You could also do some simple statistics using the COUNTIF formula to display, for example, how many visitors put a given question in their "top three", "top five", or "top ten" in rank, which might be useful information.

Normally one would put the rows containing the sums, COUNTIFs, etc., at the bottom of the list, but I would suggest putting these rows at the top, right under the header column, so that you don't have to scroll to the bottom to see the results.

I hope this helps.

Keep Excelling.

Damon