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.
Best Answer
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.