Weighted Average on School grades

[RichKat]

Hello,
I have a sheet that has the words "test", "exam" or "lesson" in the cells in range C6:C47. Each of those words has a numeric value assigned in the range AA2:AB4. I would like my formula to convert the words in column "C" to the numeric values and then do a weighted average with SUMPRODUCT.
The weighted average is no problem with the following formula if I would have numeric values in Column C:

=SUMPRODUCT(C6:C47,AA2:AB4,2,1,E6:E47)/SUM(C6:C47,AA2:AB4,2,1)
But I need the text values to stay.
I tried this:
=SUMPRODUCT(VLOOKUP(C6:C47,AA2:AB4,2,1),E6:E47)/SUM(VLOOKUP(C6:C47,AA2:AB4,2,1))
But it gives a "VALUE" error.
Can you point me the right direction?
Thanks a bunch,
Richard

 

[Eric W]

How about:

=SUMPRODUCT(E6:E47,SUMIF(AA2:AA4,C6:C47,AB2:AB4))/SUMPRODUCT(SUMIF(AA2:AA4,C6:C47,AB2:AB4))

 

[Scott R]

I think this works:
=SUMPRODUCT(COUNTIF(C6:C47, AA2:AA4) * AB2:AB4) / COUNTA(C6:C47)

edit:
I didn't understand what was in E:E, but if it's a student's grades, try this:
=SUM(E6:E47) / SUMPRODUCT(COUNTIF(C6:C47, AA2:AA4) * AB2:AB4)

 

[RichKat]

How about:

=SUMPRODUCT(E6:E47,SUMIF(AA2:AA4,C6:C47,AB2:AB4))/SUMPRODUCT(SUMIF(AA2:AA4,C6:C47,AB2:AB4))

Thanks, Erik! That works perfectly! I was, as usual, trying to make it more complicated than it actually was and didn't quite understand in practice how SUMIF() works until I worked through your formula.

Thanks again.
Richard
​

 

[RichKat]

Hi Scott,
Thanks for the thoughts. That doesn't give the correct results so I'm not sure where it goes wrong. The most basic formula I could find for calculating Weighted Averages in Excel is SUMPRODUCT(WEIGHTS_RANGE,VALUES_RANGE)/SUM(WEIGHTS_RANGE), So I was needing to plug my WEIGHTS texts into the WEIGHTS_RANGE in that formula which, if I understand Eric's formula, the SUMIF does. Thanks again.

 

[Eric W]

One thing to remember is that the lookup functions (VLOOKUP, HLOOKUP, LOOKUP, OFFSET, INDEX, INDIRECT) do not generally work in array formulas. But if what you're trying to look up is a number, then sometimes SUMIF will work instead.

Glad to help!

 

[orlahirish]

How about:

=SUMPRODUCT(E6:E47,SUMIF(AA2:AA4,C6:C47,AB2:AB4))/SUMPRODUCT(SUMIF(AA2:AA4,C6:C47,AB2:AB4))

>>>>

Quoted line is collapsed

>>>>

customwritingcompany​

Shouldn't we use the double negative (- ) to convert the required values into numeric?

 

[Eric W]

Welcome to the forum!

Great question! You see this kind of formulation with a SUMPRODUCT a lot:

=SUMPRODUCT(--(AA2:AA20="test"),AB2:AB20)

This formula would essentially add up any numbers in AB2:AB20 that have "test" in AA2:AA20. Note that only the first condition has the -- on it. This is because that is a condition that returns TRUE/FALSE values. So you'd end up with an array of {TRUE,FALSE,TRUE, ...}. SUMPRODUCT is designed to treat any non-numeric value as zero, so all of those would be treated as zero. If you use the -- in front of it, then it converts TRUE/FALSE to 1/0, and SUMPRODUCT handles it OK. But if the range already contains numbers, you don't need the --. So in this example, and the formula from post 2, all the ranges were already numeric so they didn't need the --. E6:E47 were numeric, and the results from the SUMIF were numeric.