Grade Distribution

[Marie1uk]

Hi there

I am trying to perfect a spreadsheet that will analyse the stats of grades. Their name, gender, predicted grade and actual grade are entered into columns A, B, C & D respectively, commencing in cell B38.

I am trying to perfect a formula that will tell me the grades of BOYS only that score D,M,P and FAIL (D=Distinction, M=Merit, P=Pass) and express it as a % of the BOYS only grades.

As a test I entered the grades & genders of 3 boys in cells A38-D40, inputting D for distinction in all three actual grade colums. I also inputted grades for 4 girls too. In cell Y5 I used the following formula to calculate the % of boys getting a D as an actual grade (which should be 100% as all 3 boys got D).

=SUMPRODUCT(--($B$38:$B$131="M"),--($D$38:$D$131="D"))/ COUNTIF($B$38:$B$131,"?*")

The result of this formula is 42.9% which is the right result IF I wanted the % of BOYS & GIRLS that got a D. I am having brain freeze here - anyone want to point out the obvious and help me just to isolate the boys grades?

[SteveG]

Try,

=SUMPRODUCT(--($B$38:$B$131="M"),--($D$38:$D$131="D"))/ COUNTIF($B$38:$B$131,"M")

HTH

Steve

[Marie1uk]

Thanks Steve :P