A novel way to weight exam grades

When I was in grad school, my major professor had a novel method for weighting exam grades in the physiology lecture he taught.  Rather than dropping the lowest of four exams or just averaging them all equally (i.e., 25% each), he had a novel approach: make the highest grade worth 35% of the final score and make the lowest grade worth 15%.  This doesn't overly penalize the lowest score nor let it fly, and it is similarly fair to the highest grade.  However, it makes calculating the grades somewhat difficult at the end of the semester, especially when there are between 70 and a hundred students, and they need their final grades turned in by the very next afternoon (when the final falls on a Thursday... as it always seemed to for us).  I was his grader, so the labor involved was a motivating factor when I realized I didn't want to go through that again the next semester.  We were recording the grades in Excel, but he resorted to doing his non-linear math using a calculator and paper for the intermediate steps.

Believe it or not, this weighted calculation can be performed even though it is conditional (and without manually rearranging values into "highest score" and "lowest score" columns or some such).  Instead, I used the MAX and MIN functions to single out these values.  The next step is just a logical leap.  See, you just average them all as being worth 25% each.  *After* that, you can apply the "bumps."  Since 35% is just 25% + 10%, you then get Excel to take 10% of the highest grade and add that to the average, then subtract 10% of the lowest grade.  I configured a spreadsheet that would do this automatically (you just used the MAX and MIN functions to find the relevant scores from the series), and I immediately knocked several hours of work (and who knows how many errors) off of the process.

Assuming the four exam grades are in cells A1, B1, C1, and D1, the expression is:

Copy and paste as necessary.

The math isn't intuitive, and that's why few educators adopt this approach.  Most professors prefer to drop a grade for the simple (and lazy!) reason that it's easy to calculate an average with a missing (or deleted) value.  The problem is that students often "blow off" an exam.  In many cases, this was the first exam of the semester that they felt they could disregard.  Unfortunately, when the material grew more difficult in later portions of the course, they then had another low score at a point when their average couldn't accept any more casualties, and often this was after the drop date.  Making even the lowest grade count made sense.  Making it not hurt too much made even more sense.  And rewarding the best work is natural as well.  People get promotions or a bonus for an exceptional performance, not just for consistently doing well.  Having your best disproportionately recognized in the calculation feels natural, just like discounting your worst as part of your performance, just on a bad day.

All contents copyright Alexplorer.
Back to the Misc. Bits