teaching: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.

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.

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.

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

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

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 4.0 International