I am a teacher, summarizing ACT results. I have over a hundred students who have each taken the ACT for four years, anywhere from one to four times each year. For example, I am trying to calculate an overall average Composite score for 2017 for the following data.
<tbody>
</tbody>
I want an average of each student’s max Composite score for 2017. So I want
AVERAGE(MAX(27,29,28), MAX(20), MAX (19,16))
which should equal 22.67.
So… I have tried using an array formula.
{AVERAGE(IF(B2:B10=”Composite”,MAX(C2:E10))}
But I get 31.
So I tried
{AVERAGE(IF(B2:B10="Composite",MAX(C2:C10,D2:D10,E2:E10)))}
But I still get 31.
So instead of finding the max of each composite row, it’s just finding the max of the C2:E10.
Any idea how to do this? BTW - I am a long-time reader, first-time poster. Thanks!
A | B | C | D | E | |
1 | 2017 | 2017 | 2017 | ||
2 | Student A | English | 28 | 31 | 27 |
3 | Math | 27 | 27 | 28 | |
4 | Composite | 27 | 29 | 28 | |
5 | Student B | English | 22 | ||
6 | Math | 18 | |||
7 | Composite | 20 | |||
8 | Student C | English | 21 | 18 | |
9 | Math | 17 | 14 | ||
10 | Composite | 19 | 16 |
<tbody>
</tbody>
I want an average of each student’s max Composite score for 2017. So I want
AVERAGE(MAX(27,29,28), MAX(20), MAX (19,16))
which should equal 22.67.
So… I have tried using an array formula.
{AVERAGE(IF(B2:B10=”Composite”,MAX(C2:E10))}
But I get 31.
So I tried
{AVERAGE(IF(B2:B10="Composite",MAX(C2:C10,D2:D10,E2:E10)))}
But I still get 31.
So instead of finding the max of each composite row, it’s just finding the max of the C2:E10.
Any idea how to do this? BTW - I am a long-time reader, first-time poster. Thanks!