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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"LrdNikon" wrote in message
...
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?

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


LrdNikon wrote:
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?