Finding average based on criteria (non-contiguous cells)

Question

I was wondering if someone could assist me. I am trying to research it myself but have become stuck.

Here is a sample of a Subject Mark-Book. Different units have different grade boundaries attached to them, so I have put them into a separate tab and then used Vlookups to give the grade depending on the unit.

I am then trying to find the average for each unit, for each student. So for example, Unit 1 average for Joe Bloggs. Unit 2 average for Joe Bloggs etc. We sometimes teach multiple units at a time (depending if there is more than one teacher on the class) so I cannot group each unit together (that would make it easier, I suspect).

I have tried:

Averageifs - however, I can't seem to get it working based on the criteria (which would be unit). Indexmatch - same issue

I have looked into array formulas. I managed to build a very simple one, but my knowledge isn't that great YET but I will learn.

We can have about 6 - 7 assessments per unit, so I am trying to make it easier for myself and all the other teachers.

Thank you for any help. I am just a simple teacher, trying to make a difference to our pupils.

https://docs.google.com/spreadsheets/d/1UGclvfTodE_NwhSrFaDlj6rUIfriRLMoQc7ZODfKHCg/edit?usp=sharing

Answer 1

Use array formulas, { array expressions } and query(), like this:

=arrayformula( iferror( vlookup( I2:I & H2:H, { Grades!$L2:$L & Grades!$M2:$M, Grades!$N2:$N }, 2, false ) ) )`

=arrayformula( query( 
  { 
    Markbook!B2:B & " " & Markbook!C2:C, Markbook!I2:I, iferror(Markbook!H2:H/0, Markbook!H1), Markbook!H2:H, Markbook!J2:J; 
    Markbook!B2:B & " " & Markbook!C2:C, Markbook!L2:L, iferror(Markbook!K2:K/0, Markbook!K1), Markbook!K2:K, Markbook!M2:M; 
    Markbook!B2:B & " " & Markbook!C2:C, Markbook!O2:O, iferror(Markbook!N2:N/0, Markbook!N1), Markbook!N2:N, Markbook!P2:P 
  }, 
  "where Col2 is not null", 
  0 
) )

=query( 
  Data!A1:E, 
  "select A, B, avg(D) 
   where A is not null 
   group by A, B", 
  1 
)

=arrayformula( query( 
  { 
    iferror(A2:A/0, A1), A2:B; 
    iferror(D2:D/0, D1), D2:E 
  }, 
  "where Col2 is not null", 
  0 
) )

See your sample spreadsheet.