# Finding average based on criteria (non-contiguous cells)

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.

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
) )
``````