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))),"")
arrayformula
?, why you want to reference both Sheet A and Sheet B? This wouldn't be a homework assignment would it?the list of classes is massive and may not have any students with a test score, they are not necessarily sorted alphabetically and continuously added to and modified
That bit of extra information would have been helpful in the body of the question. Ditto:there are other bits of information on those sheets
if that information is germane to this question. AAMOI, is the data on Sheet2 the raw score data? FWIW, I'm intrigued why you would generate the data on Sheet1 other than by a function.normalisation
. The idea is to reduce data redundancy and improve data integrity. 'Sheetclass_AverageScore' is redundant if it is not generated dynamically; more data on Sheet B and this range could be out-of-date. "So what", you ask (quite reasonably)... focus on the data; generate reports, analysis, etc directly from the data so that output is always up-to-date.