Using Excel to convert numeric grades to letter grades


This is an Excel spreadsheet that lets you enter numeric grades. Excel then automatically computes a letter grade. It is based on a scoring scheme where the grade boundaries are separated by 10 points: 90 points is the boundary between an A and a B; 80 points is the boundary between a B and a C, and so on.
Grade sub-divisions occur at 3-1/3 intervals. Thus, any grade between 80 and 83.333 points is a B-, but grades between 83.334 points and 86.666 points are designated as B.

You can download the spreadsheet by clicking on the image of the spreadsheet and select "Save as..." to save it to your desktop. Once you have downloaded the spreadsheet, open it and then change the numeric value of any student's grade. As you change the numeric value, the corresponding letter grade will also change automatically.

In practice, you should insert several columns into this spreadsheet to enter individual student test scores, then use Excel to compute the final score. Once the final numeric score is calculated, Excel will then find the appropriate letter grade.

This spreadsheet uses the INDEX function. Column E in the spreadsheet uses a table of grade descriptions (e.g. "D+") to describe a particular grade. The index function takes a numeric value and returns the appropriate description. In this spreadsheet, the grade descriptions have been placed in cells E2 through E15, where "D+" is located in cell E5 (the 4th cell from the top). We can recover this description using the Index function as follows:

=INDEX(E2:E15,4,1)


where E2:E15 specifies the range of cells where the grade descriptions are located, 4 indicates the position of the cell whose contents we wish to display and 1 indicates the number of cells we wish to display.

There are a few additional complexities in the spreadsheet. First, absolute reference ($E$2:$E$15) are used rather than relative references. Second, there are some numeric conversion factors to convert the numeric grade into an integer from 1 to 14 which is then used as an index into the list of grades.
The numeric conversion factors for this spreadsheet can be altered to accommodate different numerical grade scales. For example, it is also possible to use a grade scale where A is 4.0, B is 3.0, etc.