Array Formula - ACT scores

LisaB114

New Member
Joined
Jan 2, 2019
Messages
5
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.

ABCDE
1201720172017
2Student AEnglish283127
3Math272728
4Composite272928
5Student BEnglish22
6Math18
7Composite20
8Student CEnglish2118
9Math1714
10Composite1916

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

Some videos you may like

Excel Facts

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,761
Office Version
2010
Platform
Windows
A​
B​
C​
D​
E​
F​
G​
1​
Student
Section
2017
2017
2017
Max
2​
Student AEnglish
28​
31​
27​
31​
F2: =MAX(C2:E2)
3​
Student AMath
27​
27​
28​
28​
4​
Student AComposite
27​
29​
28​
29​
5​
Student BEnglish
22​
22​
6​
Student BMath
18​
18​
7​
Student BComposite
20​
20​
8​
Student CEnglish
21​
18​
21​
9​
Student CMath
17​
14​
17​
10​
Student CComposite
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
Joined
Jan 2, 2019
Messages
5
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
Joined
Aug 18, 2015
Messages
10,099
Try:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))))}

<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

New Member
Joined
Jan 2, 2019
Messages
5

ADVERTISEMENT

Got it! Thanks, Eric!
 

LisaB114

New Member
Joined
Jan 2, 2019
Messages
5

ADVERTISEMENT

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
Joined
Aug 18, 2015
Messages
10,099
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top