# Array Formula - ACT scores

#### LisaB114

##### New Member
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!

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### shg

##### MrExcel MVP
 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

##### New Member
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

##### MrExcel MVP
Try:

ABCDE
1201720172017
2Student AEnglish283127
3Math272728
4Composite272928
5Student BEnglish22
6Math18
7Composite20
8Student CEnglish2118
9Math1714
10Composite1916
11
12English24.66667
13Math21
14Composite22.66667

</tbody>
Sheet5

Array Formulas
CellFormula
C12{=AVERAGE(IF(B\$2:B\$10=B12,SUBTOTAL(4,OFFSET(C\$2:E\$2,ROW(C\$2:C\$10)-ROW(C\$2),0))))}

</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

##### New Member

Got it! Thanks, Eric!

#### LisaB114

##### New Member

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

##### MrExcel MVP
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

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

You're welcome!

1,106,345
Messages
5,510,768
Members
408,809
Latest member
Matthiasek

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...