Excel Grade Formula (Table of Contents)
The formula for Grade in Excel
The Formula for Grade is the nested IF formula that checks certain criteria and returns the specific grade if the criteria are met. Finding the student grade is probably the basic example all the excel guides give to their students to explain the logic of logical functions in excel. IF, AND, OR, FALSE, TRUE, and IFERROR are some of the famously used logical functions in excel.
This article will explain the different ways of finding the grade for student score, employee efficiency level, bonus calculation, and many other things using the Formula for Grade in Excel.
How to use Formula for Grade Calculation in Excel?
Let us understand how to use the formula for Grade Calculations through some examples.
Each example will explore a different use case implemented using the Formula for Grade in Excel.
Find Grades of Students: Nested IF Conditions
Finding student’s grades is one of the best examples to understand the logicality of the IF condition in excel. Consider the below students score for this example.
Below are the criteria for finding a grade.
- If the score is above 550 Grade is A+
- If the score is above 500 grade is A.
- If the score is above 450, graded is B+
- If the score is above 400 grade is B
- If the grade is above 300 grade is C
- IF the grade is not matching any of the above conditions, then the grade will be FAIL.
In order to find an individual student grade, we need to test all these conditions, and based on the satisfaction of the conditions; we need to declare the grades.
By using the IF condition, we can find the grades here. Before I tell you how to find the grades, let me explain the IF condition first.
Logical Test: This is the argument to test the logic of the value.
Value if True: If the logical test is true, what should be the result we want.
Value if False: If the logical test is false, what should be the result we want.
Now take a look at the simple example.
Example #1
If the day is equal to SUNDAY, then the price should be 35 or else we need the price as 30.
Step 1: Open the IF condition in cell B2.
Step 2: Now, we need to do the logical test, i.e. whether the day is equal to SUNDAY or not. Select the cell A2 and test whether that is equal to SUNDAY or not.
Step 3: If the logical test is true, then the result is 35.
Step 4: If the logical test is the false result should be 30.
Step 5: Close the bracket and hit the Enter key. We have a result.
Step 6: Now, we will drag this formula of cell B2 to cell B3. So we will have a result as shown below.
Example #2
Now let’s apply this logic to find the grades of students by using Formula for Grade in Excel.
23 Online Courses | 9 Hands-on Projects| 110+ Hours| Verifiable Certificate of Completion
4.9
View Course
Step 1: Open the IF condition in the C2 cell. The first logical test is if the score is above 550. So test the score is >550 or not if this test id TRUE value should be “A”.
Step 2: If the test is false value should be??? Here we need to test a few more criteria. So open the new IF condition.
Step 3: Now test the second criteria, i.e. if the value is >500. If this logical test is the true result should be “B+”.
Step 4: Now, if both the conditions are FALSE, we need to test the third criteria. So open one more IF condition and test whether the value is >400 or not. If the test is TRUE, the result should be “B”.
Step 5: Like this, apply IF conditions for all the criteria and close the brackets. I have already applied all the IF conditions and got the results.
Step 6: Now, we will drag this formula of cell C2 to cell C9. So we will have a result as shown below.
Example #3
Find Grades of Employees: Combination of IF & AND Conditions
We have seen how to use the IF condition to find the grades of students. Now let’s see the combination of IF & AND condition.
I have an employees data table with their department and year of service in the company.
In order to find the grade, the criteria are “If the employee is in Sales Department & Year of service is >=5 years then the employee should be eligible for Promotion”.
Apart from the Sales department nobody is eligible for Promotion.
Here we need to test two conditions at a time, unlike the previous one, where we need to test many criteria’s individually. A combination of IF & AND condition can help us here.
AND function tests multiple conditions at a time, and if all the passed conditions are correct, then we will get the result as TRUE or else FALSE.
Step 1: Open IF condition in D2 cell.
Step 2: Logical test is not single here; rather, we need to test two conditions for one employee. So open AND function inside the IF condition.
Step 3: First criteria is whether the department is equal to Sales or not. And the second test is whether the year of service is >=5 years or not.
Step 4: Since we have only two conditions to test, close the bracket of AND function. If the logical test by AND function is TRUE, then the result should be Eligible for Promotion, and if the logical test by AND function is FALSE, then the result should be Not Eligible Promotion. Close the bracket and hit Enter key to complete the formula.
Step 5: Now, we will drag this formula of cell D2 to cell D9. So we will have a result as shown below.
Things to Remember
- If you are finding the grade by testing multiple conditions at a time and if all the conditions should be true, then we need to use IF & AND conditions to get accurate results.
- If you are finding the grade by testing multiple conditions at a time, and if any one of the conditions is true, then we need to use IF & OR conditions to get the accurate results. OR condition returns a TRUE value if any one of the supplied conditions satisfies, and it will return FALSE if none of the conditions is not satisfied.
- When you are finding the grade on the basis of numerical values, we need to pass the highest value first. It should be in decreasing order.
- All the text values should be enclosed with double quotes, and numerical values need not to be enclosed with double quotes.
Recommended Articles
This has been a guide to Formula for Grade in Excel. Here we discussed How to use Formula for Grade Calculation in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
