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:
=AVERAGE(A1:D1)+(0.1*MAX(A1:D1))-(0.1*MIN(A1:D1))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.
|Back to the Misc. Bits|