Dropping a Lowest Score

I have a lab class, where I drop the lowest score students receive, easy enough. Except, all items are not weighted equally, (lab A might be worth 10 points, while lab B might be worth 23 points, and so on).

Right now, what I do in an excel file is calculate the total points earned and the maximum points, except in each instance I remove a different assignment from the calculation. Then compare the various percentages and drop the one that yields the highest overall percentage.

TL;DR, here is the main question part ;)

Is this a problem that can be solved more easily, or can I answer the question of which to drop with a formula? I'd love to include the calculation in my grade book so it happens automatically. Right now, I have to go in and drop an assignment manually for each student, since my only option is to drop "lowest score" which doesn't work since 5/10 has a different impact than 3/30. (I realize I could scale everything and make them all worth the same amount, but that complicates other parts of the grade for me, so isn't ideal)

I've included a screen shot of what I do in excel for hopeful clarity.

The bottom three rows are what I look at.

Total Score w/o: Total earned points (from row 2) without the lab corresponding to that column

Total Max w/o: Total maximum points possible (from row 3) without the lab corresponding to that column

Combined Percent w/o: Just the values from $\displaystyle\frac{\text{Total Score w/o}}{\text{Max Score w/o}}$ for each column.

I have conditional highlighting which shows me the highest percentage, so in this case, I would drop the student "Hydrate" lab assignment from their grade.

*Note: I will confess I really wasn't sure what tags I should use. This stackexchange is way out of my comfort zone, so most terms were not familiar. Feel free to change them to whatever is most appropriate.

• Find the percentage of each individual item. Multiply by weight % divided by 100. Then add the results. Should do it for you. Commented Jan 11, 2017 at 0:45
• I changed the tags to something more appropriate, but it's still not quite right. Can't think of something better at the moment though. Commented Jan 11, 2017 at 0:45
• I doubt it can be solved more easily (your rule means that you cannot always simply drop the worst percentage in row 4), but a descriptive formula could say something like $\text{Maximum}\left(\dfrac{\text{Total Score} - \text{particular lab score }}{\text{ Total Potential Score} - \text{potential for that particular lab} } \right)$ Commented Jan 11, 2017 at 0:57
• I think Henry's suggestion is the way to go, especially since there really is no need for a more "elegant" formula if it's all just staying in a spreadsheet anyway (although it does make sense to at least think about whether one exists!). If you get the syntax right Excel can certainly make this calculation in a single cell, so that you wouldn't even need an extra row or anything. Commented Jan 11, 2017 at 3:02
• Ouch! Without these sorts of aggregate operations, I don’t think that you can really do anything reasonable directly in the gradebook.
– amd
Commented Jan 13, 2017 at 20:15

Let $t_i$ and $s_i$ be the possible points and actual points scored, respectively, for each individual assignment, and let $T$ and $S$ be the respective sums over all assignments. Assuming that the goal is to produce the highest percentage for the student, you’re trying to maximize ${S-s_i\over T-t_i}$. With a bit of algebraic manipulation, this can be rewritten as $\frac S T+{St_i-s_iT\over T(T-t_i)}$, so drop the assignment that maximizes ${St_i-s_iT\over T(T-t_i)}$.

For the small example in your comment to Travis’ answer, these values are $0.129$, $-0.017$, $-0.026$ and $-0.060$, so the first score should be dropped. For the slightly larger data set in those comments, this method selects the 15/35 score ($0.036$), just as you had originally computed. For the example in your question, “Hydrate” is the winner with a value of $0.045$, so it should be dropped, which also agrees with the method you’d been using.

Of course, if you’re doing this in a spreadsheet anyway, you can compute ${S-s_i\over T-t_i}$ for each assignment and have the spreadsheet find the highest value for you, as Henry points out in his comment to your question.

Figure out the the average value of an assignment, say 25 points. Leave all the scores intact, but before you average, deflate the denominator by 25 points. Truncate any score that happens to go over 100 down to 100.

This gives a break to students who do poorly on an assignment (or maybe two), gives a slight advantage to students who perform consistently, and makes your life a lot easier.

You can tell students you're "in effect" dropping the lowest score, and they will probably never be able to figure out that's not exactly what you're doing. Or tell them exactly what they're doing and they can do the math to see they're still getting a roughly equivalent break for a missed or botched assignment.

I've done this for the past 10 years with no complaints.

• Sorry, can you give an example? I kind of follow, but I think I'm missing something along the way
– J M
Commented Jan 11, 2017 at 2:01
• Sure, 5 lab assignments worth 25 points each. One student has scores 20, 25, 2, 20, 20. Drop the lowest you get (20 + 25 + 20 + 20)/100 = 85%. Do it my way and you get (20 + 25 + 2 + 20 + 20)/(125-25) = 87%. I'm not saying it is a perfect system, but it is a simple one that is good enough for me. Commented Jan 11, 2017 at 2:17
• I see, I will consider that. From the one test student I used it on, it raised their score 3% from what it was by my more labor intense method. I'll have to look at it more carefully and decide if that would inflate things too much. It definitely has simplicity in it though!
– J M
Commented Jan 11, 2017 at 2:23
• Remember that 'dropping the worst' might encourage students just to skip one lab with no effort at all. Commented Jan 11, 2017 at 2:27
• This is true. They do have a limited number of labs they can miss before they receive an F, so hopefully that dissuades them from making that choice too frivolously.
– J M
Commented Jan 11, 2017 at 2:35

Let the total points be $S$, the amount of possible points on an assignment be $K$, and the score the student got on the assignment be $x$.

$${SCORE} = x\frac{S}{K}$$

You'll have to apply this to every equation, and drop the one with the lowest result. In your examples, the student has scores of $1/30, 5/7, 15/25$ and, $13/17$. This means the total score is $30+15+25+17 = 87$. You then apply this to the equation: for the first assignment, $$10\frac{87}{30} = 29$$ For the second assignment, $$5\frac{87}{7} \approx 62.14$$ The third; $$15\frac{87}{25} = 52.2$$ And finally, $$13\frac{87}{17} \approx 66.53$$

Because the first assignment yeilds the lowest result, it has the largest negative effect on the grade, and should be dropped.

This works because you're taking the weight of the assignment $\frac{Points}{Total Points}$, then multiplying it by the score, yielding the negative weight on the total grade.

• Can you elaborate this? If I have 4 assignments (10/30, 5/7, 15/25 and 13/17), how do I calculate with this formula their final percentage or score? Right now, this just uses their percentage on an assignment and scales it by the total points for all the assignments.
– J M
Commented Jan 11, 2017 at 1:12
• @JM, there you go Commented Jan 11, 2017 at 1:40
• @JM, using that set, I got to drop the 2/10 score, which yields a 64.81% average, so it doesn't work, but one of us did something wrong. Commented Jan 11, 2017 at 2:24
• If you drop the 2/10, points earned $=16+28+15+21+5+20+16+7+7 = 135$, and total points $=20+30+35+21+17+36+16+19+17=211$. $\frac{135}{211} \approx 0.639810$, or $\approx$ 64%. Commented Jan 11, 2017 at 2:33
• @JM must've done something wrong entering it into a calculator Commented Jan 11, 2017 at 2:37