Average Index-Match with Multiple Matches

jbiehl

Board Regular
Joined
Jul 30, 2020
Messages
59
Office Version
  1. 365
Platform
  1. Windows
I am trying to average students' scores by standard.
In the example below, I need a formula to return the average of Tom's Standard "RL.2" Quiz Scores (cells B14 and C14) in the Standard Average for "RL.2" (cell H2).

I'm thinking it must include AverageIF-Index-Match, like below, but since the Index-Match only returns the first value, it's current form isn't working out.
=AVERAGEIF(A13:E16,INDEX(A13:E16,MATCH(G14:G16,A13:A16,0),MATCH("*RL.2*",A13:E13,0)))

*Note: this will be implemented on a much larger scale with hundreds of assignments, so Index-Match nested in the Small or Large formulas, where I have to copy the formula hundreds of times is not an option.

Thank in advance!

1596140781844.png
 

Attachments

  • Average Index Match.PNG
    Average Index Match.PNG
    21 KB · Views: 5

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi & welcome to MrExcel.
How about
+Fluff New.xlsm
ABCDEFGHI
13RL.2 Quiz 1RL.2 Quiz 2RL.3 Quiz 1RL.3 Quiz 2RL.2RL.3
14Sally0306090Tom3595
15Joe104070100Sally1575
16Tom205080110Joe2585
Data
Cell Formulas
RangeFormula
H14:I16H14=AVERAGE(FILTER(FILTER($B$14:$E$16,$A$14:$A$16=$G14),ISNUMBER(SEARCH(H$13&"*",$B$13:$E$13))))
 
Upvote 0
If you put this in H1 and drag right and down
=AVERAGEIF($B$13:$E$13, H$13&"*", INDEX($B$14:$E$300, MATCH($G14, $A$14:$A$300, 0),0 )
 
Upvote 0
Solution
Thanks so much to the both of you!
Both formulas worked!
Now I have a follow-up question...

I put the formula that averaged their quizzes in a new tab called, "Roll-up". There are separate tabs for various sources which provide the standard data. For example, A12:E16 above might be in a tab called "September_Gradebook" but there is also a "June_Gradebook".

Is there a way to tell Excel to automatically add late-start/new students to the end of 'Roll-Up'!A:A (student column), if they weren't in the initial roster, but then show up in another student column of a different tab, like, 'March_Gradebook'!A:A?

Thanks again!
 
Upvote 0
As that is a totally different question, you will need to start a new thread.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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