Excel Grade Formula (Table of Contents)
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 is 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 these are some of the famously used logical functions in excel.
In this article, we 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 formula for Grade Calculations through some examples.
Each example will try to explore a different use case which is implemented using the Formula for Grade in Excel.
Find Grades of Students: Nested IF Conditions
Finding students grade 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 condition then grade will be FAIL.
In order to find an individual student grade, we need to test all these conditions and based on the conditions satisfaction we need to declare the grades.
By using IF condition we can find the grades here. Before I tell you how to find the grades let me explain 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.
Formula for Grade in Excel – Example #1
If the day is equal to SUNDAY then 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 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 the cell B3. So we will have a result as shown below.
Formula for Grade in Excel – Example #2
Now let’s apply this logic to find the grades of students by using Formula for Grade in Excel.
Step 1: Open the IF condition in C2 cell. 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’s. 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 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’s 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 the cell C9. So we will have a result as shown below.
Formula for Grade in Excel – Example #3
Find Grades of Employees: Combination of IF & AND Conditions
We have seen how to use IF condition to find the grades of students. Now let’s see the combination of IF & AND condition.
I have employees data table with their department and year of service in the company.
In order to find the grade, criteria is “If the employee is in Sales Department & Year of service is >=5 years then employee should be eligible for Promotion”.
Apart from 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. Combination of IF & AND condition can help us here.
AND function tests multiple conditions at a time and if the 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 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 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 the 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 condition to get the 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 condition to get the accurate results. OR condition returns TRUE value if any one of the supplied condition satisfies and it will return FALSE if none of the conditions are 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 downloadable excel template. You can also go through our other suggested articles –
