Dropping the Lowest Grade
- Leave a Comment
- Shortlink
- 3 min
In past semesters, I have assigned students weekly quizzes. As a sign of weakness, I customarily dropped the two lowest quiz grades for each student so that missing or bombing those two quizzes didn’t negatively impact their final grade. The integrated grade book in Blackboard allows you to set up a category of assignments (such as “Quiz”) and drop the n lowest (or highest) grades for the entries in that category, but I dropped Blackboard this semester so I can’t use this feature anymore. However, that won’t require dropping grades by hand. Writing a formula in a spreadsheet can automate this process.
Enter Your Data
Arrange the quiz grades in a single row, one row per student. In this example, I’ve listed the seven totally fake quiz grades for three totally fake students. The possible points for each quiz is 10.
| Name | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Carolyn D. Larabee | 5 | 5 | 7 | 8 | 5 | 1 | 10 |
| 2 | Rosemarie D. Cooper | 8 | 10 | 2 | 7 | 10 | 7 | 4 |
| 3 | Max J. Cervantes | 1 | 6 | 4 | 8 | 8 | 1 | 10 |
Finding the Low Grades
The SMALL function in any spreadsheet software allows you to find the nth smallest value in an array. If you arrange all those quiz grades in a single row for each student, you can have your spreadsheet find the lowest value.
SMALL (Array, Ranking)
In our case above, use the following code to get the lowest value for Carolyn D. Larabee, an underachieving student who, you’ll note, doesn’t actually exist:
SMALL (B1:H1,1)
This returns the lowest grade, a pathetic “1” in this case. You now have to subtract this value from the calculation since you don’t want to count it in the student’s grade. You just simply add up all the quiz attempts and then subtract the offensively low grade:
SUM(B1:H1) - SMALL (B1:H1,1)
If you want to omit the two lowest grades, as was my usual practice, then add a second SMALL function and have it subtract the smallest and the second smallest value.
SUM(B1:H1) - SMALL (B1:H1,1) - SMALL (B1:H1,2)
You’ll notice that the first SMALL function finds the first smallest value, and the second SMALL function finds the second smallest value. You’ll also notice that explaining spreadsheet functions results in really bad grammar.
Keeping the High Grades
You can get an average of the five remaining quizzes by adding all the quiz attempts, subtracting the two lowest quizzes, and then dividing all of that by the number of quizzes (5) remaining:
( SUM(B1:H1) - SMALL (B1:H1,1) - SMALL (B1:H1,2) ) / 5
If you don’t want an average, you can project the score by adding all the attempts, subtracting the two lowest scores, multiplying all that by 7 and finally dividing everything by 5.
( SUM(B1:H1) - SMALL (B1:H1,1) - SMALL (B1:H1,2) ) * 7 / 5
This would be useful if, for instance, you based the total quiz score on 70 points (7 quizzes x 10 points each) and wanted to boost the student’s score by nullifying the two lowest scores and still get a meaningful score relative to those 70 points.
Your grading policy likely differs from mine, but you can adapt these formulas to conform to your policies. Good luck.
