Fairly basic Excel help required

Rwac · 26 Feb 2008 at 12:14

Feel like a plonker but I cant seem to get my head round this.

I have a table of survey results. The left axis is the question number (1-40).
The top axis is the respondent number, there are 120 respondents.
Each question had several multiple choice answers, these have been represented by numbers. (ranging 1 to 7).

It has been suggested a basic way of segmenting the respondents is to use frequency tables, and cross tabulations.

I thought a way to start on it would be to create a formulae, that gets applied to all 130 respondents answers to question 1. This formulae would be totally up the number of '1' answers, '2' answers, '3' answers and such. I am not sure if this is right, nor how to do it (i must fail at google and excel help).

It is the first time i have had to do something mathematical within excel for a while, last time i did math was 5 years ago at AS Level, and my brain is rusty.

Any help or pointers would be much appreciated.

http://img75.imageshack.us/my.php?image=excelas1.gif

I have had a stab at hosting a little sample

Thanks!

helmet · 26 Feb 2008 at 12:18

COUNTIF

http://office.microsoft.com/en-us/excel/HP052090291033.aspx

Rwac · 26 Feb 2008 at 12:35

helmet said:
COUNTIF

http://office.microsoft.com/en-us/excel/HP052090291033.aspx

Fantastic! haha thankyou!

From your mathematically minded brains, is this the best thing to do to achieve what i need to achieve?

MTA99 · 26 Feb 2008 at 12:47

Goatyman said:
Fantastic! haha thankyou!

From your mathematically minded brains, is this the best thing to do to achieve what i need to achieve?

How can we suggest how to achieve something if we don't know what it is we are supposed to be achieving :confused:

Could you use histograms for each question to show the distribution?

Rwac · 26 Feb 2008 at 12:54

MTA99 said:
How can we suggest how to achieve something if we don't know what it is we are supposed to be achieving

Could you use histograms for each question to show the distribution?

Ok sorry right. The survey explores the respondents reactions to 2 new product concepts. It takes 4 attributes (performance, ease of use etc) and asks the respondent to rate it on a scale of 1-7, then also rate 2 existing products in the market on the same attributes/scale.

The aim of this part is to segment the men's toiletrie users market. So, those who are 20-24, on 25k+, who prefer long lasting shaving products etc.
A histogram is a good idea, but there are 41 questions so it could get messy. The lecturer suggests frequency tables (which i am doing now using COUNTIF), and also cross tabulations, which i think will become clearer once i have done these formulae.

MTA99 · 26 Feb 2008 at 13:19

Goatyman said:
Ok sorry right. The survey explores the respondents reactions to 2 new product concepts. It takes 4 attributes (performance, ease of use etc) and asks the respondent to rate it on a scale of 1-7, then also rate 2 existing products in the market on the same attributes/scale.

Still think histrograms are good for an initial overall impression. You'd only need 4 graphs, one for each attribute then use colours to plot the 4 different products on the same graph. Should be immediately obvious how your concept compare to the competition for each attribute.