multiple columns of data, reporting Means of specific combinations
 

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.

multiple columns of data, reporting Means of specific combinations
 

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.

multiple columns of data, reporting Means of specific combinations
 

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.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steve" wrote:

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.