Skip to main content
Web Applications

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

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? Commented 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 :) Commented 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. Commented 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). Commented 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. Commented Jun 28, 2021 at 4:14