Here is an annual grade sheet for 7 students in an institution, with names and marks. We also have grades based on the range of scores.
Method 1 – Using the VLOOKUP Function to Calculate Letter Grades in Excel
Steps:
- Change the Score from a range to single numbers as VLOOKUP calculates, automatically defining the series of numbers as a table array.
- Insert this formula in cell D5:
=VLOOKUP(C5,$F$7:$G$11,2,TRUE)
Here, C5 defines the student score we want to calculate for the letter grade. Cell range F7:G11 is the table from which the lookup value will be returned. 2 is the column number in the lookup table to return the matched value. TRUE is for an approximate match.
- Press Enter.
You can see the letter grade for the required mark.
- Use the AutoFill tool to apply the same formula for all marks.
Read More: How to Make a Grade Calculator in Excel
Method 2 – Calculating Letter Grades with the IF Function
Steps:
- Insert this formula in cell D5:
=IF(C5>=90,"A",IF(C5>=80,"B",IF(C5>=70,"C",IF(C5>=60,"D","F"))))
Here, the IF function is applied to return one value based on the condition given for the selected cell, defining whether it is true or false. Therefore, if the student gets more than 90, the letter grade will be A, and chronologically marks less than 60 will get F.
- Press Enter.
You can see the letter grade is visible beside the marks.
- Drag the bottom corner of cell D5 to get the output for all the marks.
Read More: Calculate Grade Using IF function in Excel
Method 3 – Applying the IFS Function in Excel to Calculate Letter Grades
Steps:
- Insert this formula in cell D5:
=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")
Here, the IFS function checks whether one or more conditions have been successfully completed. Then, it returns a value that corresponds to the first TRUE condition. The conditions are based on the source dataset.
- Press Enter.
You will see the letter grade based on the mark.
- Drag the bottom corner of cell D5 to get the output for all the marks.
Read More: How to Make Automatic Marksheet in Excel
Method 4 – Inserting the LOOKUP Function for Letter Grades Calculation
Steps:
- Change the range of scores to minimum numeric values of that respect range as per this image:
- Insert this formula in cell D5:
=LOOKUP(C5,$F$7:$F$11,$G$7:$G$11)
Here, C5 defines the student score we want to calculate for the letter grade. Cell range F7:F11 is the reference from which the lookup value will be returned. Lastly, G7:G11 is the source cell range of scores.
- Press Enter.
- Use the AutoFill tool in cell range D6:D11 to get the final output.
Read More: How to Make Result Sheet in Excel
Method 5 – Finding Letter Grades Using Excel VBA Macro
Steps:
- Go to the Developer tab and select Visual Basic.
- Select Module from the Insert section.
- Insert this code on the blank page:
Sub InsertLetterGrade()
Dim i As Integer
Dim rslt As String
For i = 5 To 11
If (Cells(i, 3) >= 90) Then
result = "A"
ElseIf (Cells(i, 3) >= 80) Then
result = "B"
ElseIf (Cells(i, 3) >= 70) Then
result = "C"
ElseIf (Cells(i, 3) >= 60) Then
result = "D"
Else
result = "F"
End If
Cells(i, 4).Value = result
Next i
End Sub
- Click the Run Sub button or press F5.
- Click the Run in the Macros window.
You will get all the letter grades at a time in cell range D5:D11.
Read More: How to Apply Percentage Formula in Excel for Marksheet
Method 6 – Combining IF and AND Functions to Calculate Letter Grades in Excel
Steps:
- Insert this formula in cell D5:
=IF(AND(B5,C5>=60),"Pass","Fail")
Here, the IF function returns one value when a condition is true and defines another value if it’s false for the selected cell. The AND function tests the condition if the grade exceeds or exceeds 60. If all the conditions are correct, we will get the TRUE result. Otherwise, the result will be FALSE.
- Press Enter.
There you will see the output.
- Apply the AutoFill tool to get results in cell range D6:D11.
Read More: Excel Formula for Pass or Fail with Color
Things to Remember
- When you have a percentage instead of numbers, you must define % in the logical function to get letter grades.
- Lock the selected range before dragging the formula to other cells.
- In the case of calculating the grades for a situation where the less number means a higher grade, then use “<” instead of “>” as the operator.
Download the Practice Workbook
Get the sample file here to practice.
Related Articles
- How to Calculate Subject Wise Pass or Fail with Formula in Excel
- Calculate Grades with Weighted Percentages in Excel
- How to Calculate Percentage of Marks in Excel
- Calculate Average Percentage of Marks in Excel
- How to Calculate Grade Percentage in Excel
- Calculate College GPA in Excel
