Array Formula - ACT scores

L

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Student A[/TD]
[TD]English[/TD]
[TD]28[/TD]
[TD]31[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD]27[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Composite[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Student B[/TD]
[TD]English[/TD]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Composite[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Student C[/TD]
[TD]English[/TD]
[TD]21[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Math[/TD]
[TD]17[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Composite[/TD]
[TD]19[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]
A​
[/td][td="bgcolor:#C0C0C0"]
B​
[/td][td="bgcolor:#C0C0C0"]
C​
[/td][td="bgcolor:#C0C0C0"]
D​
[/td][td="bgcolor:#C0C0C0"]
E​
[/td][td="bgcolor:#C0C0C0"]
F​
[/td][td="bgcolor:#C0C0C0"]
G​
[/td][/tr][tr][td="bgcolor:#C0C0C0"]
1​
[/td][td="bgcolor:#F3F3F3"]
Student​
[/td][td="bgcolor:#F3F3F3"]
Section​
[/td][td="bgcolor:#F3F3F3"]
2017​
[/td][td="bgcolor:#F3F3F3"]
2017​
[/td][td="bgcolor:#F3F3F3"]
2017​
[/td][td="bgcolor:#F3F3F3"]
Max​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
2​
[/td][td]Student A[/td][td]English[/td][td]
28​
[/td][td]
31​
[/td][td]
27​
[/td][td="bgcolor:#E5E5E5"]
31​
[/td][td]F2: =MAX(C2:E2)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
3​
[/td][td]Student A[/td][td]Math[/td][td]
27​
[/td][td]
27​
[/td][td]
28​
[/td][td="bgcolor:#E5E5E5"]
28​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
4​
[/td][td]Student A[/td][td]Composite[/td][td]
27​
[/td][td]
29​
[/td][td]
28​
[/td][td="bgcolor:#E5E5E5"]
29​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
5​
[/td][td]Student B[/td][td]English[/td][td]
22​
[/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
22​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
6​
[/td][td]Student B[/td][td]Math[/td][td]
18​
[/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
18​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
7​
[/td][td]Student B[/td][td]Composite[/td][td]
20​
[/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
20​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
8​
[/td][td]Student C[/td][td]English[/td][td]
21​
[/td][td]
18​
[/td][td][/td][td="bgcolor:#E5E5E5"]
21​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
9​
[/td][td]Student C[/td][td]Math[/td][td]
17​
[/td][td]
14​
[/td][td][/td][td="bgcolor:#E5E5E5"]
17​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
10​
[/td][td]Student C[/td][td]Composite[/td][td]
19​
[/td][td]
16​
[/td][td][/td][td="bgcolor:#E5E5E5"]
19​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
12​
[/td][td][/td][td]English[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
24.7​
[/td][td]F12: =AVERAGEIF($B$2:$B$10, B12, $F$2:$F$10)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
13​
[/td][td][/td][td]Math[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
21.0​
[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]
14​
[/td][td][/td][td]Composite[/td][td][/td][td][/td][td][/td][td="bgcolor:#E5E5E5"]
22.7​
[/td][td][/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
Try:

ABCDE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2017[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Student A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]31[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]27[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Math[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]28[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Composite[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]28[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Student B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Math[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Composite[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Student C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Math[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Composite[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]English[/TD]
[TD="align: right"]24.66667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Math[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Composite[/TD]
[TD="align: right"]22.66667[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C12[/TH]
[TD="align: left"]{=AVERAGE(IF(B$2:B$10=B12,SUBTOTAL(4,OFFSET(C$2:E$2,ROW(C$2:C$10)-ROW(C$2),0))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Question Question
SEQUENCE formula
Replies
0
Views
114
GMC The Macro Man
GMC The Macro Man
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
496
etaf
etaf
V
  • Solved
Arrays in VBA - error 13 type mismatch
2
Replies
18
Views
560
Alex Blakenburg
A
D
  • Solved
Return High Value for multiple instances
Replies
5
Views
648
Fluff
Fluff
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
82
welshgasman
welshgasman

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top