Can I combine SUMIF and CHOOSE ?

[palaeontology]

I have the following 'dummy' data ...

As you can see there are 5 exams, and three students.

As the year progresses, the exams become more and more difficult and so their weighting reflects this increase in difficulty.

In J7 I have the following formula ...

it obviously has two components ... a SUMPRODUCT and a SUMIF

You can see in green (in columns K and L) what each of these components comes to ... obviously, Mike and Aryu have both missed an exam or two and so their weighted total out-ofs differ from Joe as he has sat all 5 exams.

I then experimented with trying to achieve the same result when the cells are not contiguous .....

I had to change the SUMPRODUCT formula in V7 to allow for the fact the cells are not contiguous. So I used the CHOOSE function.

as you can see by the correct calculations in green in column V, SUMPRODUCT has no problem combining with CHOOSE.

However, when I try to combine CHOOSE with SUMIF in column W, I get an error. This is my formula ...

Can SUMIF combine with CHOOSE, or have I done something wrong ?

Kind regards,

Chris

 

[BigBeachBananas]

Hi,
I believe that there's a simple fix:
You should express your weights as percentages instead of whole numbers i.e 5=>.05
Then you can simply do a SUMPROD of your weights and test scores. No longer have the need to divide by the SUMIF.

 

[Jeffrey Mahoney]

Tell me if I'm wrong. You need to provide the total possible score of each test say in row 3.
=Sumproduct(D4:H4,D7:H7)/sumproduct(D4:H4,D3:H3)

My belief is that the factor you use to weight the tests doesn't matter. Although I have preferred values that added up to 100 like you have

 

[BigBeachBananas]

Hi,
I believe that there's a simple fix:
You should express your weights as percentages instead of whole numbers i.e 5=>.05
Then you can simply do a SUMPROD of your weights and test scores. No longer have the need to divide by the SUMIF.

Your final weighted averages are incorrect for Mike and Aryu: It should be 72.5 and 59.6 , respectively

 

[Marcelo Branco]

Try

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2
3
4			Weighting	5			10		15						20				50
5
6				Test 1 (%)			Test 2 (%)		Test 3 (%)						Test 4 (%)				Test 5 (%)	Result
7			Joe	68			72		64						83				76	74,80
8			Mike	82			81								79				89	85,29
9			Aryu				64		78										83	79,47
10
Plan6

Cell Formulas
Range	Formula
T7:T9	T7	=SUMPRODUCT(--ISNUMBER(SEARCH("Test",D$6:S$6)),D$4:S$4,D7:S7)/SUMPRODUCT(--ISNUMBER(SEARCH("Test",D$6:S$6)),--(D7:S7<>""),D$4:S$4)

M.

 

[StephenCrump]

Duplicated here: weighted averages considering missing data

with a generalised solution similar to Marcelo's.