Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add new data to specific columns in multiple worksheets | New Users to Excel | |||
fit multiple values into specific value (combinations?) | Excel Worksheet Functions | |||
I have 5 columns of data and want to create combinations based on | Excel Discussion (Misc queries) | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions | |||
Need combinations of values from a list to add up to a specific Va | Excel Worksheet Functions |