Letter Grades in Excel
It is simple to get Excel to assign letter grades, with our without pluses and minuses. The key is the LOOKUP function.
Somewhere in your spreadsheet you must include a table matching letter grades to number grades. The grades should be in ascending order, and by each letter grade should be the minimum numeric grade needed to achieve that letter grade. An example for a typical 10-point scale is shown below, in the range A10:B21.
Course grades should be kept in a column. Each cell of the letter grade column should contain a formula of the form:
=LOOKUP(NumericGrade,NumericCutoffList,LetterGradeList),
where
-
NumericGrade is the cell holding the student's numeric course grade.
-
NumericCutoffList is the range of cells holding the minimum scores for each letter grade.
-
LetterGradeList is the range of cells holding the corresponding letter grades.