palaeontology
Active Member
 Joined
 May 12, 2017
 Messages
 414
 Office Version

 2016
 Platform

 Windows
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
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