DesignWIKI

Fil Salustri's Design Site

COLLECTIONS

2015.01.09 11:13

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.

• 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.

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:

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.

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 ))

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 )

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