In this sample dataset the columns are labelled Name, Marks, and Grades. For the grades, we have 5 possible options.
Method 1 – Computing Grades in Excel Using the IF Function
The IF function in Excel performs a logical test and returns two different values based on the result being True or False.
Steps:
- Double-click on cell D5 and enter the following formula:
=IF(C5>=90,"A",IF(C5>=80,"B",IF(C5>=70,"C",IF(C5>=60,"D","F"))))
- Press Enter to return a grade in cell D5.
- Copy the formula to the rest of the cells using the Fill Handle.
Read More: Calculate Grade Using IF function in Excel
Method 2 – Using the VLOOKUP Function to Compute Grades
The VLOOKUP function searches for data in a table organized vertically. Here this function is used to look up data in the Score-Criteria table in our dataset. The Score column should be organized in an ascending manner starting from 0 and going to 90.
Steps:
- Double-click on cell D5 and type in the below formula:
=VLOOKUP(C5,$F$5:$G$9,2,TRUE)
- Press Enter to return a grade in cell D5.
- Copy the formula to the rest of the cells using the Fill Handle.
Read More: How to Calculate Letter Grades in Excel
Method 3 – Applying IFS Function to Calculate Grades
We can use the IFS function to test multiple conditions without multiple IF statements.
Steps:
- Click on cell D5 and enter the following formula:
=IFS(C5>=90,"A",C5>=80,"B",C5>=70,"C",C5>=60,"D",C5<60,"F")
- Press Enter to return a grade in cell D5.
- Copy the formula to the rest of the cells using the Fill Handle.
Read More: How to Calculate Grade Percentage in Excel
How to Count the Number of Students with Each Grade
The COUNTIF function can be used to count the number of students who received each grade. This function counts the number of cells in a range that meet specific criteria.
Steps:
- Double-click on the cell G5 and enter the below formula:
=COUNTIF($D$5:$D$10,F5)
- Press the Enter key to confirm the formula.
- Copy the formula to the rest of the cells using the Fill Handle.
Read More: How to Calculate Final Grade in Excel
Download Practice Workbook
You can download the practice workbook from here.
Related Articles
- How to Calculate Subject Wise Pass or Fail with Formula in Excel
- Make Automatic Marksheet in Excel
- How to Calculate Grades with Weighted Percentages in Excel
- Excel Formula for Pass or Fail with Color
- How to Calculate GPA in Excel
- How to Average Letter Grades in Excel