Extracting stats dependant on criteria

[Lewis.P]

I'm looking for a method of extracting data depending on a criteria and performing stats all within a single formula.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Participant ID[/TD]
[TD]Genders[/TD]
[TD]Scores[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]M[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]M[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]F[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]F[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]M[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]F[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]M[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]F[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]F[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

The data that I am looking to extract:
The mean male score.
The median male score.
The mode male score.
The range of male scores.
The frequency of male score of 8.
The standard deviation of male scores.

I've worked out the mean formula i think:
=SUM(SUMPRODUCT(--(Genders="male"),(Scores))/TotalMaleParticipants)
But I'm struggling with the others

In the past I have simply extracted the data depending on the gender in to two new tables and performed the statistical anaylsis on the new tables. But this is becoming a little overwhelming as the number of variables is around 20 (gender, age, ability level, etc.), the number of participants is over 300 and the number of factors with scores is around 20 so the work involved is giving me a massive head ache. I am hoping there is a method of doing this 'dissection' of the data and analysis within a single formula.

I have probably overlooked a very simple method :/

 

[Gary's Student]

Consider the following array formulas:

[TABLE="width: 286"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=AVERAGE(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MODE(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MEDIAN(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MAX(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MIN(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=STDEV(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Lewis.P]

Consider the following array formulas:

[TABLE="width: 286"]
<tbody>[TR]
[TD]=AVERAGE(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MODE(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MEDIAN(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MAX(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=MIN(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
[TR]
[TD]=STDEV(IF(B$2:B$11="M",C$2:C$11))[/TD]
[/TR]
</tbody>[/TABLE]

Ahhhhh of course! I had overlooked the use of array formulas. Thank you!
Do you know how to best construct the 'frequency of a particular score given by males' formula?
I think I may have managed it with this but again I've probably overlooked something easier:
=SUMPRODUCT(--((Genders)="Male"),--((Scores)="8")))



Thanks again

 

[Gary's Student]

You are correct:

=SUMPRODUCT((B2:B11="M")*(C2:C11=8))

 

[Lewis.P]

Awesome, thank you very much.