Automatically assign grades based on raw scores

Teachers frequently use Excel to record performance of students. Using formulas, teachers calculate ratio (or percent) of each output to the overall grade, usually within the range of 0 to 100. These raw scores are usually converted into particular grading systems, such as into A to E or 1.00 to 4.00.

There is a function in most spreadsheet programs (e.g., Microsoft Excel, LibreOffice Calc, OpenOffice.Org Calc, or Google Sheets) that will automatically search the raw score's equivalent grade, meaning convert a 65 to, say, "E." This is VLOOKUP.

To use VLOOKUP, you must first create a table which the spreadsheet program will use as reference. VLOOKUP looks up a table vertically for values based on an item you told the computer to look for, and returns a corresponding value, based on the reference table. The value you tell the computer maybe an actual value (i.e., you enter the value manually within the cell) or a reference cell.

For example, you have a table of students with three columns. The first column is Name, the second column is raw grade (in number), and the third column is where you intend to enter the grade equivalent (e.g., A to E).

You enter the student records (Student Name and Raw Grade). Here's how it should look like:

Name	Raw Score	Grade
Student A	61
Student B	75
Student C	92
Student D	68
Student E	91

Then you create the reference table (preferably in another sheet, so that you can re-arrange your student record sheet without affecting the reference table). Your reference table should have three columns: 1) Minimum Grade; 2) Maximum Grade; and 3) Grade Equivalent.

You should arrange the grade ranges from lowest to highest going down. Here's an example:

Grade Minimum	Grade Maximum	Grade Equivalent
0	60	E
61	70	D
71	80	C
81	90	B
91	100	A

After you do this, you return to your student record sheet. In the third column (you do this in one cell only, then just copy the formula to the other rows), you use the VLOOKUP function to tell the computer to do the following (the formula will follow, don't worry):

1. Find the cell in the column Raw Grade in the reference table (which, again, is recommended placed in another sheet)
2. When found, return the Grade Equivalent in this cell.

To do this, you use this formula:

=VLOOKUP(Put_here_the_cellreference_of_the_raw_grade, Reference_table, Column_Number_of_the_Grade_Equivalent)

In most spreadsheet programs, you don't need to type the whole function manually. You can start with typing the function, then use the mouse to identify the references, particularly the reference table which is in another sheet.

Check that the formula you enter returns the correct grade equivalent. Once you verify that it is correct, you have to ensure that the reference for the reference table is absolute, meaning you use the $ sign for the reference key.

=vlookup(B2,'Grade Conversion Key'!A$2:C$6,3)

(The 'Grade Conversion Key' is the name of the second sheet. When you enter the variables of the function, you can use your mouse to point to the location of the cell or range you want.)

After you make sure the reference for the reference table is already absolute, you can now copy the formula to the rest of the rows in the Grade column, as follows:

Name	Raw Score	Grade
Student A	61	=vlookup(B2,'Grade Conversion Key'!A$2:C$6,3)
Student B	75	=vlookup(B3,'Grade Conversion Key'!A$2:C$6,3)
Student C	92	=vlookup(B4,'Grade Conversion Key'!A$2:C$6,3)
Student D	68	=vlookup(B5,'Grade Conversion Key'!A$2:C$6,3)
Student E	91	=vlookup(B6,'Grade Conversion Key'!A$2:C$6,3)

Notice here that while the reference for Raw Score cell changes, the reference for the reference table doesn't. Here's how it should look like:

Name	Raw Score	Grade
Student A	61	D
Student B	75	C
Student C	92	A
Student D	68	D
Student E	91	A

The formula works for Microsoft Excel, LibreOffice Calc, OpenOffice.Org Calc, and Google Sheets.

To see how it work in Google Sheets, click here.

ADDENDUM: If your Raw Score has more decimal places than those in your reference table (e.g., your reference table values have up to 2 decimal places but your raw scores in your cell references have 3 or 4 decimal places), you need to round off the values according to the number of decimal places in your reference table before using the VLOOKUP process. To do so, you need to use the ROUND function of your spreadsheet application. The formula would be like this, if you need to round off to 2 decimal places:

=vlookup(ROUND(B2,2),'Grade Conversion Key'!A$2:C$6,3)