If you are using Microsoft Excel for your grade book, the following directions should help you to create a vertical lookup table for translating your numerical grades into letter grades.
- Open your Excel grade book for a class.
- Select the Sheet 2 tab at the bottom of the open worksheet.
- In cell A1, type Score. In cell B1, type Grade.
- In cell A2, type the minimum score for an F grade (probably 0). In cell B2, type the letter grade F. Note: It is important to start your table with the lowest grade.
- Continue to place in columns A and B, the minimum scores and matching grades.
- Name your table: Highlight the range of cells that comprise your table (e.g., A1:B10). Click Format on the menu bar. Click Define. Type a name such as Sociology 1 and click the OK button.
- Double check the table. Sometimes Excel anticipates your next entry and inputs an incorrect entry. For example, if you typed B-, the program might anticipate that the next entry is also B- when you actually want B.
- Go back to Sheet 1, your grade book. Create a column for your grades (Grade) next to the Total column (the column where you have summed your grades for each student).
- In the top cell of the Grade column, enter the lookup function as follows (don't forget the equal sign):
=VLOOKUP(Cell that holds the first total, Name of Sheet 2 table, 2) and enter.
For example: =VLOOKUP(H3, Sociology1, 2) and enter.
This should assign a grade to the total in H3.
- Copy the formula down the Grade column.
Note: Check many of the grades to make certain the lookup function operates correctly. You are responsible for your grades.