0

I'm trying to calculate a weighted average for thousands of students.

I can do this for individual learners in turn (SUM PRODUCTS).

The problem is I don't know how to do this for thousands of students at the same time.

Each student has a different number of grades, and each grade has a different weight.

All I found was sorting the data according to the Student Code. And trying to make the given formula count for me as long as the key is the same, it repeats itself.

Please help how I can do this

enter image description here

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
QarRaQ
  • 1
  • 1

1 Answers1

0

There's AT LEAST two easy ways to do it. Both "filter" for values desired, then do the arithmetic.

One is straightforward and uses the SUMPRODUCT() you look to be happy with, but performs filtering on the list of values in order to choose appropriate items to work upon. It uses FILTER() to choose the rows that qualify, then uses FILTER() again to choose the columns from the inner FILTER()'s results:

=SUMPRODUCT(  FILTER(  FILTER(B1:D12, B1:B12=I1),  {0,1,0}),  FILTER(  FILTER(B1:D12, B1:B12=I1),  {0,0,1}))

The formula, as presented, assumes a lookup entry cell of I1 for the student to look up. And of course, the ranges for the columns would be thousands, not 12.

However, for working with "THOUSANDS" one can simply use as much of column I as needed, just changing the address to a range. OR, if one (as seems likely), also means "All" when saying "thousands" one can use UNIQUE() with its third parameter set as FALSE to extract a list of single instances of each student name that is in those "thousands." However, even if creating that array, it seems one cannot just use I1# to capture the whole result. I believe this to be due to the differing lengths of the lookup range and the shorter output of UNIQUE().

FILTER() actually creates a list of TRUE/FALSE results for the rows included in it. The "trick" here is that you get the columns you desire by directly typing that list (for example: {0,1,0} to select the 2nd column). A time when hardcoding is actually useful, though you can definitely create those arrays in ways that let the formula be dynamic. Just no need ever, here.

Bear in mind, one could use INDEX() to select the columns to use for SUMPRODUCT()'s arrays. Personally, I think this is easier, but there's not a lot of difference.

So much for fancy. A more old-school method, one that will work with fairly old versions of Excel, just do range comparisons to "filter" the data and present it to SUMPRODUCT():

=SUMPRODUCT(  IF(B1:B12=I1, C1:C12, 0),  IF(B1:B12=I1, D1:D12, 0)  )

Each array in the function is supplied by the IF()'s which simply compare the lookup range to the column I cell for the row (yes, neither approach will be SPILL formulas so you must copy down as far as the UNIQUE() in column I extends) and return the two columns data into the function's parameters.

Direct, clear and simple, easy to understand and maintain over the years. Should be fast since it uses simple TRUE/FALSE tests, not functions. Neither approach uses SPILL functionality in their SUMPRODUCT() column so no edge there to either.

But this one uses only functionality that you a clearly familiar with so why not?

And actually, the obnoxious part, making a list of the unique students, uses SPILL so that task works nicely as well.

Jeorje
  • 1