How to Compute Grades in Excel (3 Ways)

In this sample dataset the columns are labelled Name, Marks, and Grades. For the grades, we have 5 possible options.

 

how to compute grades in excel


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"))))

Compute Grades Using IF Function in Excel

  • Press Enter to return a grade in cell D5.

Compute Grades Using IF Function in Excel

  • 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.

how to compute grades in excel

Steps:

  • Double-click on cell D5 and type in the below formula:
=VLOOKUP(C5,$F$5:$G$9,2,TRUE)

how to compute grades in excel

  • 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")

Applying IFS Function in Excel to Compute Grades

  • Press Enter to return a grade in cell D5.

Applying IFS Function in Excel to Compute Grades

  • 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.

How to Count Number of Students in Excel Having Certain Grades

Steps:

  • Double-click on the cell G5 and enter the below formula:
=COUNTIF($D$5:$D$10,F5)

How to Count Number of Students in Excel Having Certain Grades

  • 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

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo