As you may already know, one of the many benefits of Microsoft Excel is the ability to generate and produce calculations through the use of formulas. Instructors can easily benefit from this feature by setting up a grade book within Excel and using formulas to quickly and easily calculate student grades. In addition, they can also use a formula to automatically generate a letter grade.
The VLOOKUP function in Excel allows you to automatically calculate a letter grade for each student by utilizing a grade chart within the spreadsheet. Here’s how to put it together:
- Open the grade book.
- Create a column where the calculated letter grade will appear.
- Somewhere within the spreadsheet, create a separate table. In the first column, you’ll input the minimum score required for a letter grade and in the second column, you’ll put the actual letter grade. When creating the table, make sure grades are ordered from lowest to highest.
- Select the corresponding Grade Letter cell for the first student and click on the Function icon above the spreadsheet.
- In the dialog box that appears, enter VLOOKUP in the search field and click on Go.
- From the provided selections, click on VLOOKUP to highlight it and click on OK.
- In the Function Argumentsdialog box, you are required to enter three values:
- Lookup_value: This field is the cell that contains the final grade for the student. Click on the cell to enter the correct value. (In our example grade book, we’ll start with the first student’s final grade, which is cell G6).
- Table_array: This is the letter grade table you created in Step 3. In the spreadsheet, click and drag to highlight the entire able. Now, enter a dollar sign ($) before each letter and each number in the field (it should look like $D$17:$E$22). This will prevent the table range from changing when we later apply the formula to for other students in the spreadsheet.
- Col_index_num: This field tells Excel which column to use in the letter table to return a value. Since we want to display the actual letter grade, enter the number 2.
You’ll now see that a letter grade has been entered in the cell you selected. To apply the formula for all other students, you can do a simple copy/paste into each cell in the Letter Grade column.