DesignWIKI

Fil Salustri's Design Site

Site Tools


teaching:assigning_letter_grades_in_excel

Assigning Letter Grades in Excel

How to use EXCEL to quickly calculate letter grades from percentage grades.

While most instructors assign grades on a scale out of 100, letter grades are reported to the Registrar. Assuming you use D2L or Excel to track grades, there's an easy way to have Excel generate the letter grades that correspond to given percentage (or any other) grades.

Using D2L

D2L can't do the conversion. But you can download an excel-compatible form of the grade center from D2L.

  • Start your browser.
  • Enter your D2L course shell.
  • Go to the Grade Center.
  • Above the grades table, to the right, you will see a button marked Work Offine.
  • Select the Download option from Work Offline.
  • You will be presented with a screen of options. The options are fine as they are.
  • Click Submit.
  • On the next screen, click Download.
  • An excel-readable file will be downloaded.

Using Excel

We assume:

  • you have an excel spreadsheet with one row per student, and
  • each row has one cell containing the percentage grade for that student.

Somewhere in the spreadsheet, you must create a table that has two columns.

  • The first column contains the letter grades.
  • The second column contains the lowest percentage grade for the corresponding letter grade.

Here's an example:

Grades Cutoff
F 0
D- 50
D 53
D+ 57
C- 60
C 63
C+ 67
B- 70
B 73
B+ 77
A- 80
A 85
A+ 90

Next, you use the excel LOOKUP function to map a given grade to a letter grade.

The general form of the LOOKUP function is:

LOOKUP ( Student grade cell, Range of cutoffs, Range of letter grades )

Remember to use dollar signs to fix the location of the range of cutoffs and range of letter grades.

  • For example, if you've used columns A and B, starting at row 1, for the table above, you could write the following:
LOOKUP ( C16, $B$1:$B$13, $A$1:$A$13 )

You can write this formula for only one cell, then copy and paste it into the rest of the cells.

You can download a sample EXCEL spreadsheet that can be used as a template for this.

Using Google Spreadsheets

If you're using Google Spreadsheets, you need to do it slightly differently. Google doesn't support the LOOKUP function - indeed, it remains in Excel for backward compatibility only.

The difference is minor: you just need to use a different equation. Instead of LOOKUP, use this:

INDEX ( range of letter grades, MATCH ( student grade cell, range of cutoffs ))

Rounding grades before processing

You may have a student grade of 89.7 which you want to turn into A+. The above technique will not do this because 89.7 is still less than the cutoff for A+.

To fix this, you need to ROUND the percent grade before doing the lookup. Here's the same example with the rounding operation included.

LOOKUP ( ROUND (C16,0), $B$1:$B$13, $A$1:$A$13 )

See Also

References

<refnotes> notes-separator: none </refnotes>

teaching/assigning_letter_grades_in_excel.txt · Last modified: 2020.03.12 13:30 (external edit)