Google Sheets, use arrayformula to get average score for each classroom from a list of all students in all classrooms?

Question

Whilst not actually referring to students and test scores, the following hypothetical is the easiest was for me to explain the question.

Sheet A contains a list of all classrooms, sheet B contains a list of all students (as well as what classroom they are in, and what score they got for the test). I would like to use an ARRAYFORMULA and named ranges to show the average of each classroom's test scores.

My formula below (for one classroom) was successful but it does not automatically expand.

=AVERAGE(FILTER(student_TestScore,student_ClassName=$A2))

For reference A2 contains the first classroom in the list of sheet A. I also have the following named ranges (to try and keep my formulas as robust as possible).

• 'Sheet A'!A:A = class_ClassName
• 'Sheet A'!B:B = class_AverageScore
• 'Sheet B'!A:A = student_StudentName
• 'Sheet B'!B:B = student_ClassName
• 'Sheet B'!C:C = student_TestScore

Below was my unsuccessful attempt at using ARRAYFORMULA to automatically expand the above formula.

=ARRAYFORMULA(
    IF(ROW($A:$A)=1,"Average Test Score", 
        AVERAGE(
            FILTER(student_TestScore,student_ClassName=class_ClassName)
        )
    )
)

All I end up with as the average for every classroom was a DIV/0 error OR the first student's test score (if that student is in the first classroom of the list). The ARRAYFORMULA is contained within 'Sheet A'!B1 as new classrooms are constantly added, moved and modified.

Any help would be much appreciated and thank you in advance!

Update: I ended up using @Tedinoz query suggestion as a helper function in combination with a VLOOKUP to achieve the desired effect. Here is the helper function. =QUERY(({student_ClassName,student_TestScore}),"select Col1, avg(Col2) group by Col1 label avg(Col2) 'Avg Test Score'",1)

Here is the ARRAYFORMULA function. =IFERROR(ARRAYFORMULA(IF(ROW($A:$A)=1,"Avg Test Score",VLOOKUP(class_ClassName,({help_ClassName, help_AvgTestScore}),2,FALSE))),"")

Answer 1

This answer doesn't satisfy the OP's requirement that answer must include an ARRAYFORMULA. However, it appears to achieve the same outcome and it is offered in a constructive spirit.

Try this formula:
=query(A1:C51,"select B, avg(C) group by B label avg(C) 'Avg Test Score' format avg(C) '#,##0.0'",1)

This is in cell L1 in the sample shown below.

The data would be taken from Sheet B only. The test score is formatted for one (1) decimal place; the actual averages are much more precise.

---

SAMPLE