How to Use Excel to Calculate Student Letter Grades
PETER FLOM
CLASS
In many classes, the teacher must summarize each student's work over the course of a year into a single letter grade -- most familiarly, the A through F scale. Although this will always involve some teacher judgment, it is possible to use Excel to help combine the available information into a single grade in an objective way.
Decide on the number of grades you need to combine. For example, you might want to combine eight homework assignments, one midterm exam and one final exam for a total of 10 grades.
Enter header that represent the following: the name of the student, the grade for each homework assignment, a header for the midterm and a header that represents the grade for the final. An example of this is: "Name," "HW" (with a number,)" Midterm" and "Final" in row 1. Leave a blank column to the right of each grade. Enter "Name" in cell A1, "HW1" in cell B1, "HW2" in cell D1, "HW3" in cell F1, "HW4" in cell H1, "HW5" in cell J1, "HW6" in cell L1, "HW7" in cell N1, "HW8" in cell P1, "Midterm" in cell R1 and "Final" in cell T1.
Skip one more column and enter "Raw Grade" and "Letter Grade" in adjacent cells in row 1. In the example, these would be cells V1 and W1.
Enter the data. In each cell that has a header, enter data. For example, row 2 might have "Joe," 90, 80, 70, 80, 90, 80, 90, 80, 95, 85.
Decide on a weighting scheme. Suppose you've decided that all the homework together will be 30 percent of the final grade, the midterm another 30 percent and the final exam 40 percent. Each individual homework assignment would be 30/8 or 3.5 percent.
Compute the weighted grades in the blank columns next to each raw grade. So, in the example, in cell C2 enter "=B2_0.035". Copy this to cells E2, G2, I2, K2, M2, O2 and Q2. In cell S2, enter "=R2_0.30" and in cell U2 enter "=T2*.4". Copy these cells down the columns for all your rows of data.
Compute the raw grade. This is the sum of all the weighted grades. In the example, enter "=C2+E2+G2+I2+K2+M2+O2+Q2+S2+U2". Copy this cell down the column.
Convert to a letter grade. First, decide on which numbers match which letters. For example, 90 and up = A, 80 to 89.99 = B, 70 to 79.99 = C, 60 to 69.99 = D, and below 60 = F.
This would be entered in cell W2 as "=IF(V2>89.99,"A",IF(V2>79.99,"B", IF(V2>69.99,"C",IF(V2>59.99,"D","F"))))".
- Be sure to convert all the individual grades to the same scale. If they are not on the same scale, you can multiply each by 100/(top grade) in which the top grade is the highest possible grade on that assignment.
- 1 Excel for Dummies 2013; Jeff Harvey
Related Articles
