0

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))),"")

5
  • Hi. Are there any particular/specific reasons why you want the answer to use an arrayformula?, why you want to reference both Sheet A and Sheet B? This wouldn't be a homework assignment would it?
    – Tedinoz
    Jun 26, 2021 at 8:48
  • @Tedinoz I don't require ArrayFormula per se but the reason is that the list of classes is massive and may not have any students with a test score, they are also not necessarily sorted alphabetically and are continuously being added to and modified (so I'd like some formula that can get the information based on any particular class name). As for Sheet A and B, there are other bits of information on those sheets. No, not a homework assignment, but it certainly feels like one :)
    – DakotaE
    Jun 27, 2021 at 21:47
  • but it certainly feels like one I know that feeling! 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.
    – Tedinoz
    Jun 28, 2021 at 0:39
  • Sorry about the exclusion of extra information. I'm new to stackexchange so I need some practice at getting the right balance between not enough and too much information. The reason why the information on Sheet1 is not generated via a function is because not all classes have students yet (but still need to be recorded). As for why the sheets are separated at all, they are smaller parts of a larger database. Sheet A has more information specific to the class (ID, teachers, etc), whilst Sheet B has more information specific to each student (contact details, etc).
    – DakotaE
    Jun 28, 2021 at 1:46
  • Congrats on working things out. I'm going to risk "teaching you how to suck eggs" but this is offered in a constructive sprit. There's a concept with databases (and that's what you've got on SheetB) called 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.
    – Tedinoz
    Jun 28, 2021 at 4:14

1 Answer 1

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

Sample

3
  • Not quite there on its own, but still fantastic help! I can probably use the query function on a separate sheet (and use named ranges to reference the data) and then use VLOOKUP to find that data. =query(({student_ClassName, student_TestScore}),"select Col1, avg(Col2) group by Col1 label avg(Col2) 'Avg Test Score' format avg(Col2) '#,##0.0'",1).
    – DakotaE
    Jun 27, 2021 at 21:51
  • I can probably use the query function on a separate sheet (and use named ranges to reference the data) I don't doubt it. and then use VLOOKUP to find that data Not sure why you want/need this; is it something that is mentioned in the question but that I have missed?
    – Tedinoz
    Jul 3, 2021 at 23:48
  • As I mentioned in the comment on the original question, the sheets are part of a much larger database. I already had a separate sheet for helper functions to keep things organised that way. The reason I want to use VLOOKUP instead of just using the QUERY directly is that because I'm trying to make the sheet more robust. The class list and student list are different sources of data. Not all classes have students, and not all students have classes. With VLOOKUP, my class list doesn't need to be in alphabetical order, and I can have classes with no test scores.
    – DakotaE
    Jul 5, 2021 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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