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