0

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

1

1 Answer 1

0

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.

1
  • I am in complete awe of you! I cannot thank you enough.
    – Spencer87
    Jul 6 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.