In this tutorial, I am going to show you 3 suitable ways to compute grades in excel. Although you can manually create a grade sheet, using excel will make the process very quick and easy. Also, you can compute the grades of a large number of students with the help of a few clicks. In the following section, we will see how to use the powerful functions of excel to easily compute the grades of the students.
In the dataset for this tutorial, we have the columns with the names Name, Marks, and Grades. We are using a small number of students to keep things simple. For the grades, we have 5 possible ranges. Those obtaining 90 or above will get an A grade, 80 or above B grade, and so on as we shall see in the formula. We will use the following dataset to compute grades in excel.
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. We will use this function to compute grades in Excel different conditions.
Steps:
- First, 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"))))
- Then, press Enter and you should see the grade in cell D5.
- Finally, copy the formula to the rest of the cells below using the Fill Handle.
Read More: Calculate Grade Using IF function in Excel
2. Using the VLOOKUP Function to Compute Grades
The VLOOKUP function in excel searches for data in a table organized vertically. We will use this function to look up data in the Score-Criteria table in our dataset. Note that, you should organize the Score column in an ascending manner starting from 0 and then going down to 90.
Steps:
- To begin with, double-click on cell D5 and type in the below formula:
=VLOOKUP(C5,$F$5:$G$9,2,TRUE)
- Now, press the Enter key and this will determine the grade of the student.
- Then, copy the formula of cell D5 to the cells below by dragging the Fill Handle and excel will give you the grades of all the students.
Read More: How to Calculate Letter Grades in Excel
3. Applying Excel IFS Function to Calculate Grades
We can use the IFS function to test multiple conditions without multiple IF statements. This will help us to compute grades in Excel with less complexity. Let us see how to use this function.
Steps:
- To start with, 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")
- Now press Enter and the formula will calculate the grade for cell D5.
- Lastly, copy the formula of cell D5 and fill the cells below with it using the Fill Handle.
Read More: How to Calculate Grade Percentage in Excel
How to Count the Number of Students Having Certain Grades in Excel
To count the number of students within each range, we will use the COUNTIF function in Excel. This function counts the number of cells in a range that meet specific criteria.
Steps:
- First, double-click on the cell G5 and enter the below formula:
=COUNTIF($D$5:$D$10,F5)
- Next, press the Enter key to confirm the formula.
- As a result, excel will count the number of students with the grade F.
- Then, simply drag the Fill Handle to copy the COUNTIF formula to the cells below.
- Finally, you should get the number of students within each grade.
Read More: How to Calculate Final Grade in Excel
Things to Remember
- Make sure that you type the logical operators like >=Â properly.
- If the numbers are inserted as percentages, then we need to define percentages in the formula as well.
- It is important to note that the IF function and the IFS function are not the same.
- For the first method, we need to insert the same number of closing parentheses as the number of IFÂ functions.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that this tutorial was helpful for you to compute grades in excel. You can take the methods shown in the tutorial and use them for much larger datasets. Also, if you get stuck in any step, make sure to review the detailed methods a few times. If you have any queries, please let me know in the comments.
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Â
