Calling all statisticians - Best way of analysing data from questionnaire

bobobrussel

I am implementing a questionnaire throughout my local community and need help on the best way to extrapolate the data I need. The questions ask the recipient to rate certain aspects of their neighbourhood by writing either a 0, 1 2 or 3 depending on their answer, i.e. How do you rate litter in your neighbourhood? Write 0 =No problem: 1 = Minor problem; 2 A problem or 3 = A big problem.

Once all the answers are in I will have a large number of 0's, 1's, 2's and 3's.

How should I use excel to analyse the data to identify which issues the community rate as the most important overall? Shoul I simply add them all together? Or should I only count the number of 3's for each issue.

Any advice would be most helpful.

martindow

I would add them all together and divide by the number of respondents to get an average figure. You will need to decide how to deal with odd questions that have not been answered in questionnaires. You can either count these as 1.5 (a middle score) or count it as 0 and reduce the number of respondents by 1 for that question each time it occurs.

Savvy_Sue

Not a statistician, but another (and in some circumstances equally valid) way of doing this would be to see how many 0s, 1s, 2s and 3s there were in each category. This might tell you, for example, that litter bothers some people excessively, and others not at all, but very few people are in the middle. And if you have addresses, you might spot a trend: that X road is bugged by litter, but Y road is not. Now, is that because people in Y road clear up after themselves and people in X road don't? Or some other reason ...

The 'average' which martindow describes is only one kind (and I can never remember their different names!) Another is that if you get more '2' responses than any other, then '2' is your average. Or - and this one's probably not useful here - you take the middle of the extremes, so if you get answers ranging from 2 to 3 the answer's 2.5, but if the answers range from 0 to 3 then the answer is 1.5. I think.

Devising questionnaires so you can get useful results from them is a black art, which very few master ...

kimbatty

Different ways of looking at the data would be by finding the mean , median and mode. Theres a link here which explains this in very simple terms and would give you an idea of the both the average response (mean) and the more popular trends ( mode)

http://www.manatee.k12.fl.us/sites/elementary/palmasola/mathlabtutstat1.htm

martindow

Savvy_Sue wrote:

Devising questionnaires so you can get useful results from them is a black art, which very few master ...

Excellent point - in any survey the questions can easily provoke particular responses. Probably the design of your survey is more critical than the analysis that follows it.

bobobrussel

Thanks for all your replies but I need to narow down the best (or should I say correct) way to identify the top issues. The questionnaire has 21 issues from which I shall have to identify the top 5-6 items to tackle first (work in community regeneration you see).

My problem is - the questionnaire allows residents to rate as per the following: 0 = No problem; 1 = Minor problem; 2 = A problem or 3 = A big problem...what if LITTER gets a response of 40% who think its 'A big problem', but 90% think GRAFFITI is 'A problem'. By my reckoning, graffiti ultimately should rate higher than litter.

Is there a way to gather the aggregate scoring of each issue to highlight the order of importance?

Should I use mode in this instance?

zombiecazz

Ok for a start you would not average your answers. The 0,1,2,3 do not represent numbers. They are codes for your actual answers i.e no Problem to a big problem.
For these answer I would use a mode. To analyse look at the percentage of respondent who answered 0 or 1 or 2 or 3.
eg 80% of respondents thought that littering was a Big problem - would suggest that the majority of your community think it is a problem.
BUT for this type of questionnaire I would try and get a large amount of residents to answer.
The best way to do this would be either to visit them and explain the reasoning behind your questionnaire and point out what could be the benefits to them or include a covering letter stating again why you are doing the questionnaire, what you hope to achieve doing the questionnaire and how it will benefit them.
Test your questionnaire out on some friends. Get them to answer it and then get them to explain their answers. This is so you can make sure that what you interpret from their answer is what they meant by their answer.
Make sure that you only ever ask one question within each question - not as silly as it sounds.
Hope this helps.

kimbatty

Yep as Zombiecazz has said you need to be looking at the mode for the responses against each of your questions so you see the mode response for grafitti then the mode response for litter, this in turn should help you to rate the order of importance for each issue. You're very likely though to get some issues showing the same mode number so then you need to decide how you analize those against each other by drilling down further into the answers, percentages etc so you can then place them into order of importance

bobobrussel

Once again thanks for the replies. The questionnaire is being sent inside a community newsletter to 1,750 residents. The questionnaire is A5 and can be answered in 1 of 3 ways.

1. Completed by pen and returned via FREEPOST.
2. Completed online via our website
3. Residents can also text their answers via their mobile phone (text messages cost standard rate - or free if respondent has free text allocation)
4. In addition, we will be entering all respondents into a draw to win Primark vouchers

Hopefully, by providing three ways to respond and a prize incentive, I'm hoping the response rate is greater than normal.

Fingers crossed!

kimbatty

Best of luck, hope you get loads of great responses back

Astaroth

Lies, lies, damned lies and statistics....

Whilst it is argued that statistics can be used to prove what ever you want (after all 90% of statistics are made up) is not actually correct you do need to know before hand what you want it to prove.

There are going to be many ways to interpret the data from using it as a score to work out an average and then cross compare the questions to working out the most common answer in each category through a cross comparison of individual answers (ie 80% of people think litter is a bigger issue than graffiti)

For you to have a meaningful set of results you should workout how you are going to analyse the data before sending out the questionnaires otherwise it becomes much easier for people to argue that you have manipulated your analysis to give the result you wanted.