Comparing more than two variables

[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?

 

[Bernard Liengme]

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

 

[mkahmu]

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