multiple columns of data, reporting Means of specific combinations

[Steve]

Hi,
Say I have 3 columns of data for the Math students in my school: Teacher's
Name, Gender, Test Scores. Is there a formula that I can write in a cell
that would report back the mean value of the test scores for the Girls in Mr.
SoAndSo's class? I would have separte cells to report for the Boys, then do
the same for Mrs. WhatsHerName's classes. (laid out in a grid at the top of
the spreadsheet)
I do not want to have to do manual sorts or use filters. I would like to
have the cells automatically update as I continually modify and/or enter more
data.
Thanks.

 

[Sean Timmons]

Yes, you would use SUMPRODUCT.

So, let's give our tables some ranges.

Your source table with all data is on Sheet1 from cell A1 - C30

Row 1 has your headers.

On Sheet2, you have your results table.

In cell A2, you have Mr. Soandso. B2 is Female. In cell C2, formula is:

=SUMPRODUCT(--(Sheet1!A2:A30=A2),(--(Sheet1!B2:B30=A2),Sheet1!C2:C30)/SUMPRODUCT(--(Sheet1!A2:A30=A2),(--(Sheet1!B2:B30=A2))

This will give total test score for all Females in Mr SoandSo's class
divided by number of Females in Mr. SoandSo's class.

 

[Sean Timmons]

Yes, you would use SUMPRODUCT.

So, let's give our tables some ranges.

Your source table with all data is on Sheet1 from cell A1 - C30

Row 1 has your headers.

On Sheet2, you have your results table.

In cell A2, you have Mr. Soandso. B2 is Female. In cell C2, formula is:

=SUMPRODUCT(--(Sheet1!A2:A30=A2),(--(Sheet1!B2:B30=A2),Sheet1!C2:C30)/SUMPRODUCT(--(Sheet1!A2:A30=A2),(--(Sheet1!B2:B30=A2))

This will give total test score for all Females in Mr SoandSo's class
divided by number of Females in Mr. SoandSo's class.

 

[Luke M]

You could use this array* function
=AVERAGE(IF(($A$2:$A$100="Mr. SoAndSo")*($B$2:$B$100="Male"),$C$2:$C$100))

I'm not sure how you have your grid laid out, but you could replace the text
references with cell references to make it easier to copy formula to other
cells.

*Confirm formula using Ctrl+Shift+Enter, not just enter.

 

[Luke M]

You could use this array* function
=AVERAGE(IF(($A$2:$A$100="Mr. SoAndSo")*($B$2:$B$100="Male"),$C$2:$C$100))

I'm not sure how you have your grid laid out, but you could replace the text
references with cell references to make it easier to copy formula to other
cells.

*Confirm formula using Ctrl+Shift+Enter, not just enter.