Array Formula - ACT scores

[LisaB114]

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.

	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!

 

[shg]

	A​	B​	C​	D​	E​	F​	G​
1​	Student​	Section​	2017​	2017​	2017​	Max​
2​	Student A	English	28​	31​	27​	31​	F2: =MAX(C2:E2)
3​	Student A	Math	27​	27​	28​	28​
4​	Student A	Composite	27​	29​	28​	29​
5​	Student B	English	22​			22​
6​	Student B	Math	18​			18​
7​	Student B	Composite	20​			20​
8​	Student C	English	21​	18​		21​
9​	Student C	Math	17​	14​		17​
10​	Student C	Composite	19​	16​		19​
11​
12​		English				24.7​	F12: =AVERAGEIF($B$2:$B$10, B12, $F$2:$F$10)
13​		Math				21.0​
14​		Composite				22.7​

 

[LisaB114]

Thanks! I am actually looking for a solution that doesn't involve adding any columns. The spreadsheet is already pretty full - there's four years of data. And it's looked at by a lot of people who aren't really spreadsheet-savvy. That's why I looked at the array formula.

 

[Eric W]

Try:

	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
11
12		English	24.66667
13		Math	21
14		Composite	22.66667

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula C12 {=AVERAGE(IF(B$2:B$10=B12,SUBTOTAL(4,OFFSET(C$2:E$2,ROW(C$2:C$10)-ROW(C$2),0))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[LisaB114]

Got it! Thanks, Eric!

 

[Eric W]

Glad we could help!

 

[LisaB114]

OK. New problem. If there is a student who did not take the ACT, and thus has no scores, his resulting max of ZERO brings the average down! Any ideas anyone?

 

[Eric W]

This is a little gimmicky, but should work. With the same layout as my previous post, try:

=AVERAGE(IF(B$2:B$10=B12,IFERROR(1/(1/SUBTOTAL(4,OFFSET(C$2:E$2,ROW(C$2:C$10)-ROW(C$2),0))),"")))

also with Control+Shift+Enter.

 

[LisaB114]

Wow - fast and good! It works wonderfully. Thanks a bunch, Eric!!

 

[Eric W]

You're welcome!