Using an If statement? to produce statistics
Posted by Penny on August 31, 2001 6:33 AM
I have a spreadsheet that includes the following information:
1) Student Name
2) Student age
3) Whether they are a day or evening student
4) Students gender
What I want to do is count:
a) how many day time students are:
- under 16
- gender
- 16 - 59
- gender
- 59+
- gender
b) The same for evening students
Hope you can help!
Penny
Posted by Aladin Akyurek on August 31, 2001 7:29 AM
Penny,
This type of data can be summarized using PivotTables. What follows is however formula-based in case you might be interested.
Suppose we have in A1:A7 the following sample data:
{"Name","Age","Type","Gender";
"dora",14,"day","female";
"alex",17,"day","male";
"zora",21,"evening","female";
"nomi",35,"day","female";
"gohan",15,"evening","male";
"torre",61,"day","male"}
You can contruct the following table (I did it in the same worksheet in E1:G5):
{"","Day Time Stats","";
"","female","male";
"under 16",1,0;
"16 to 59",1,1;
"over 59",0,1}
Note. "" stands for a blank cell.
In F3 enter: =SUMPRODUCT(($B$2:$B$7<16)*($C$2:$C$7="day")*($D$2:$D$7="female"))
In G3 enter: =SUMPRODUCT(($B$2:$B$7<16)*($C$2:$C$7="day")*($D$2:$D$7="male"))
In F4 enter: =SUMPRODUCT(($B$2:$B$7>=16)*($B$2:$B$7<=59)*($C$2:$C$7="day")*($D$2:$D$7="female"))
In G4 enter: =SUMPRODUCT(($B$2:$B$7>=16)*($B$2:$B$7<=59)*($C$2:$C$7="day")*($D$2:$D$7="male"))
In F5 enter: =SUMPRODUCT(($B$2:$B$7>59)*($C$2:$C$7="day")*($D$2:$D$7="female"))
In G5 enter: =SUMPRODUCT(($B$2:$B$7>59)*($C$2:$C$7="day")*($D$2:$D$7="male"))
You can construct a similar table using the same formulas where you just replace "day" with "evening".
Just because there aren't that many formulas here, I didn't try to set up the table such a way that a single or two formulas can be copied across and down.
Aladin
