Comparing more than two variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have some research data I need to tally and chart. I am looking at a
group of subjects that answered a 7-question test. I want to tally the
responses, and tally the total instances of each one compared to the others
(like how many answered yes to 1, 2, and 3 (or any other combination)). I
know pivot tables can give me 2 variables and how many of each gave an
answer, but I would like to do it for the whole test. Can anyone help me out?

For instance, example questions could be (for simplicity sake):
male or female:
age (0-20, 21-30, 31-50, 50+):
hair color (brown, black, blonde, or red):
favorite color (red, blue, green, or yellow):



I would like to know how many FEMALES, age 21-30, with BROWN hair have 'RED'
as their favorite color?
 
Let's say we have
In A2:A72 Gender (1 for M or 2 for F)
In B2:B72 age group (1,2,3...)
In 2:C72 favourite colour (1,2,3)
We will do the males first (of course!)
In F1:H1.... enter 1,2,3..... for the age groups
In E2:E5... enter 1,2,3 for colour preference
In F2 enter
=SUMPRODUCT(--($A$2:$A$72=1), --($B$2:$B$72=F$1), --($C2:$C72=$E2)
copy this across to H2 and then copy the F2:H2 down to row 5

Copy F1:H5 to K1
In the first formula change --($A$2:$A$72=1) to --($A$2:$A$72=2; copy the
formula down and across
Now you have the female data


OR: Filter the data to get females and do Pivot table
then filter to get males and made another pivot table

best wishes
 
you may be able to do these type of charts in a program called "insight
a2", it can connect to excel and do charting of more than one variable.
google for it.
Mark
 
Back
Top