weighted averages considering missing data

[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.

Joe Bloggs has sat all 5 exams, so his final weighted average is simple to calculate ...

Note: each bracket involves the corresponding exam weighting being divided by 100, because Joe sat all 5 exams and the combined weighting of all 5 exams came to 100

However, to calculate Mike Raffone's final weighted average involves excel recognising that an exam (with a weighting of 15) has been missed, so the combined weighting of the four exams Mike sat only comes to 85, so his calculation would be ....

Likewise, Aryu Shaw's calculation is adjusted because he missed two exams (whose combined weighting came to 25), so his calculation should be ...

Now I played with this years ago and came up with a way for excel to recognise missed exams and how to calculate final averages allowing for missed exams, but it took a very large number of helper columns.

So ...

QUERY #1 .... I'm interested to Know if there's a way to write a single formula that doesn't require helper columns.

QUERY #2 .... if possible, is there a way to write a customised formula that would ultimately look something like this ...

=WeightedAverageWithMissingData($D$4{D7},$E$4{E7},$F$4{F7},$G$4{G7},$H$4{H7})

This second query may be a bit of a stretch, so please don't worry about it if it's too much.

It's really Query #1 I'm hoping might have a solution.

Very kind regards,

Chris

 

[StephenCrump]

Perhaps:

	A	B	C	D	E	F	G	H	I
1
2
3
4			Weight	5	10	15	20	50
5
6
7			Joe	68	72	64	83	76	74.8
8			Mike	82	81		79	89	85.3
9			Aryu		64	78		83	79.5
Sheet2

Cell Formulas
Range	Formula
I7:I9	I7	=IFERROR(SUMPRODUCT(D$4:H$4,D7:H7)/SUMIF(D7:H7,"<>",D$4:H$4),"-")

 

[palaeontology]

That worked brilliantly, and I've now broken down the two parts of the formula so I can understand why it works.

Thankyou, so much.

Very kind regards,

Chris

 

[palaeontology]

Is there a way to do it if the cells are not beside eachother ?

For example if the test results (and their specific weighting) are not in columns D, E, F, G and H but in columns D, T, AM, BD and BW ?

Or would I need to use 5 helper cells ... perhaps CM, CN, CO, CP and CQ ... where I return the 5 relevant test results (and their specific weighting) from D, T, AM, BD and BW ?

Very kind regards,

Chris

 

[StephenCrump]

Here are a couple of ways you could approach this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Weight	5	Blah	10	Blah	Blah	15	Blah	20	Blah	Blah	Blah	50	Blah	WghtAv	WghtAv
2	CountThisColumn?	TRUE		TRUE			TRUE		TRUE				TRUE
3
4	Joe	68		72			64		83				76		74.8	74.8
5	Mike	82		81					79				89		85.3	85.3
6	Aryu			64			78						83		79.5	79.5
7
8	Check: No of tests counted	5
Sheet3

Cell Formulas
Range	Formula
O4:O6	O4	=IFERROR((B4*B$1+D4*D$1+G4*G$1+I4*I$1+M4*M$1)/(ISNUMBER(B4)*B$1+ISNUMBER(D4)*D$1+ISNUMBER(G4)*G$1+ISNUMBER(I4)*I$1+ISNUMBER(M4)*M$1),"-")
P4:P6	P4	=IFERROR(SUMPRODUCT(IF(B$2:N$2,B$1:N$1*B4:N4))/SUMPRODUCT(IF(B$2:N$2,B$1:N$1*ISNUMBER(B4:N4))),"-")
B8	B8	=COUNTIF(B2:N2,TRUE)

I'd prefer the second approach, as it's easily adapted for 6, 7 ... 10+ tests.