EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Excel Tips Excel Grade Formula

Excel Grade Formula

Madhuri Thakur
Article byMadhuri Thakur

Updated August 21, 2023

Excel Grade Formula

The Formula for Grade in Excel

The formula for grades in Excel involves using functions such as IF, Nested IF, AND, and OR to evaluate a student’s scores and calculate their grade. It benefits educators, teachers, and students who wish to monitor their academic progress and determine their grades for a subject.

To use the formula for the grade in Excel, a combination of logical functions (IF, Nested IF, AND, OR) and operators such as “>=, <=, >, <, =” must be employed. According to the grading system, these functions and operators help assign a proper grade.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

How to use Formula for Calculating Grade in Excel?

Let us learn how to calculate letter grades and percentage-based grades in Excel using various functions with the help of different examples.

You can download this Formula for Grade Excel Template here – Formula for Grade Excel Template

Example #1

Using the “IF” Function for a Single Condition

Consider the following data of scores of students. Here, we must assign remarks like pass or fail for the corresponding score. If the score is above 35, the remark should be “PASS”. If it is below 35, the function should display “FAIL”.
(Formula for Grade in Excel) Example 1

Solution:
Step 1: Select “Cell B2” and enter the formula “=IF(A2>=35, “PASS”, “FAIL”)”.
(Formula for Grade in Excel) Example 1 Sol Step 1

Explanation of formula:

“=IF(A2>=35, “PASS”, “FAIL”).“

This formula will return “PASS” if the value of Cell A2 is greater than 35 and “FAIL” if the value is less than 35.

Step 2: Press “Enter“.
The function will display “FAIL” in Cell B2.
(Formula for Grade in Excel) Example 1 Sol Step 2

Step 3: Drag Cell B2 downward.

Result: “PASS” appears on cells with a score >35 and “FAIL” on those cells with a score <35.

(Formula for Grade in Excel) Example 1 Sol Step 3

Let us calculate letter grades based on score values with the following examples.

Example #2

Using the “Nested IF” Function

The IF function in Excel can also be combined with AND/OR. In the earlier example, we used only the “IF” function for a single condition. For multiple conditions, we can use the “Nested IF” function.

In the below example of a formula for a grade in Excel, we have data on students’ total marks, and we want to calculate the grades for the obtained marks. The following are the criteria for grades:

  • If the Score is above 550, then the grade will be A
  • If the Score is above 500, then the grade will be B+
  • If the Score is above 400, then the grade will be B
  • If the Score is above 300, then the grade will be C
  • If the Score is below 300, the remark will be “FAIL”.

Formula for Grade in Excel-Example 2

Follow the steps below to calculate students’ grades using the “Nested IF” function.

Solution:
Step 1: Insert a new column beside the score column, as shown below.
Example 2 Sol step 1

Step 2: Select “Cell C2” and enter the formula
“=IF(B2>550,”A”,IF(B2>500,”B+”,IF(B2>400,”B”,IF(B2>300,”C”,”FAIL”))))”.
Example 2 Sol Step 2

Explanation of formula:

“=IF(B2>550,”A”,IF(B2>500,”B+”,IF(B2>400,”B”,IF(B2>300,”C”,“FAIL”))))”

IF(B2>550, “A): If the score is more than 550, then the student will get an A grade.
IF(B2>500, “B+” ): If the score is more than 500, then the student will get a B+ grade.
IF(B2>400, “B” ): If the score is more than 400, then the student will get a B grade.
IF(B2>300, “C” ): If the score is more than 300, then the student will get a C grade.
“FAIL”: If none of the above conditions are met, the function will display “FAIL”.

Step 3: Press “Enter”.
The formula will display a “C” grade.
Formula for Grade in Excel-Eg 2 Step 3

Step 4: Drag the bottom corner of Cell C2 to get grades for all the scores.

Result: We successfully calculated grades with the “Nested IF” function.

Eg 2 step 4

Example #3

Using the “IFS” Function

The “IFS” function in Excel also helps to calculate the letter grades. Let’s see how it works with the help of our previous example.

Solution:
Step 1: Select “Cell C2” and enter the formula
“=IFS(B2>550,”A”,B2>500,”B+”,B2>400,”B”,B2>300,”C”,B2<300,”FAIL”)”.
Eg3 sol step 1

Explanation of the formula:

“=IFS(B2>550,”A”,B2>500,”B+”,B2>400,”B”,B2>300,”C”,B2<300,“FAIL”)”

The “IFS” function quickly checks multiple conditions and returns the corresponding value to the first TRUE condition. However, properly defining the source dataset is crucial for optimal data analysis. The formula states that if the value of Cell B2>550, then display “A”. If the value exceeds 500, display “B+”, and so on.

Note: Unlike the previous example where we had to nest IF conditions one inside another, with the “IFS” function, we can accomplish the same thing using a single function – no need to nest!

Step 2: Press “Enter”.
The formula will display “C”, as shown below.
Eg 3 Sol Step 2

Step 3: Drag the cell downward, as shown below.

Result: We successfully calculated grades with the “IFS” function.

Eg 3 Sol Step 3

Example #4

Using the “VLOOKUP” Function

If the above “Nested IF” and “IFS” function is difficult for you to understand, the VLOOKUP function in Excel is simple to understand and apply. Let’s calculate the letter grade with the “VLOOKUP” function.

Tips: Before using the VLOOKUP function, remember the following points

  • Always create a lookup table as shown below because the VLOOKUP function will fetch results from the table_array.
  • Change the score from “Above 550” to single numbers “550” (in Column E below), as VLOOKUP identifies a sequence of numbers as a table_array.
  • Always sort the data in the table in ascending order to avoid errors.

Eg 4

Solution:

Step 1: Select “Cell C2” and enter the formula “=VLOOKUP(B2,$E$2:$F$6,2,TRUE)”.
Eg 4 Step 1

Explanation of the formula:

“=VLOOKUP(B2,$E$2:$F$6,2,TRUE)”
  • B2 is the cell reference (lookup_value) or value that we want to look up.
  • $E$2:$F$6 is the table range (table_array) from which the lookup value will be searched. The “$” dollar sign in the formula fixes the cells.
  • 2 is the column number or column index in the lookup table from where the function will return the result.
  • TRUE is to search for an approximate match.

The formula will search for the lookup value, i.e., “377” (value of Cell B2) in the lookup table range, “E2:F6“, and will return the approximate match from the corresponding column E, i.e., grade (Column F). If the function doesn’t find an exact match, it will return the value closest to or less than the lookup value. In our case, the exact match of 377 is not present in column E, so the function will look for the closest or lesser value than the lookup value(377), i.e., 300, and will return C, as shown below.

Note: When using the “VLOOKUP” function with a “TRUE” value, if there isn’t an exact match for a value, the function will search for the closest match less than the lookup value and return the result value from the specified column index.

Step 2: Press “Enter”.
Eg 4 Step 2

Step 3: Drag the cell to apply the same formula for all scores, as shown below.

Result: We have successfully found out students’ grades using the “VLOOKUP” function.

Eg 4 Step 3

Example #5

Using the “IF & AND” Function

In this method, we will determine grades based on individual student scores using Excel’s “IF & AND” function.

If a student scores 90 or above in all four subjects, their score will be considered “Excellent.” Otherwise, it’ll be considered “Satisfactory”. Let’s see how it works.
Eg 5

Solution:
Step 1: Insert a new column, “Remark”, as shown below.
Formula for Grade in Excel-Eg 5 Step 1

Step 2: Select “Cell G2” and enter the formula
“=IF(AND(B2>=90,C2>=90,D2>=90,E2>=90), “Excellent”, “Satisfactory”)“.
Eg 5 Step 2

Explanation of formula:

“=IF(AND(B2>=90,C2>=90,D2>=90,E2>=90),“Excellent”,“Satisfactory”)”

The “IF” function returns a value when a condition is true. The function will also return another value if the condition is false for the selected cell. The condition is tested using the “AND” function, like if the student’s score is greater than or equal to 90. If all the conditions are correct, the function will result from a value of TRUE or else FALSE.

In our case, the formula states that if all values of Cell B2, C2, D2, and EC are more than or equals to 90, then the output will be “Excellent”, or else “Satisfactory”.

Note: Each cell value should be greater or equal to 90. If any one cell has a value less than 90, then the function will display “Satisfactory”.

Step 3: Press “Enter”.
The output is displayed below.
Formula for Grade in Excel-Eg 5 Step 4

Step 4: Drag the cell downwards, as shown below.

Result: We have successfully assigned remarks for each student using the “IF” & “AND” functions.

Eg 5 Step 4

Example #6

Calculate Percentage-Based Grades in Excel Using “Nested IF” Functions

We will learn to calculate grades based on percentages using this method’s “Nested IF” function.

Consider the following data of students and their subject-wise marks. Here, we need to find out the percentage and grade for each student.
Eg 6

Solution
Step 1: Insert three columns, as shown below.
Eg 6 Step 1

Calculate the total marks of students.
Step 2: Select “Cell F2“, enter the formula “=SUM(B2:E2),” and press “Enter”.
Eg 6 Step 2

The SUM function “=SUM(B2:E2)” will return the total scores. Result 377 is displayed in Cell F2, as shown below.
Eg 6 Step 2-3

Step 3: Drag the cell downwards, as shown below.
Eg 6 Step 3

Calculate the percentage of students.
Step 4: Select “Cell G2” and enter the formula “=F2/600”.

Note: Each subject is 150 marks, and there are 4 subjects (Total marks: 150×4=600).

Eg 6 Step 4

The formula “=F2/600” gives a decimal number like 0.68233333. To convert decimals to percentages, follow the below step.

Step 5: Select “Cell G2”, go to the “Home” tab and click on the “%” sign under the “Number” group, as shown below.
Eg 6 Step 5

Step 6: Press “Enter”.
The decimal value gets converted into a percentage; see the below image.
Eg 6 Step 6

Step 7: Drag the cell downwards, as shown below.
Eg 6 Step 7

Calculate the grade for each student.
Step 8: Select “Cell H2” and enter the formula “=IF(G2>90%,”A”,IF(G2>80%,”B+”,IF(G2>=70%,”B”,IF(G2>=50%,”C”,”FAIL”))))”.
Eg 6 Step 8

The “IF” function decides if the specified condition is met and then displays the defined output according to the condition.
Step 9: Press “Enter”.
Formula for Grade in Excel-Eg 6 Step 9

Step 10: Drag Cell G2 downwards to apply the formula for all cells.
Eg 6 Step 10

Result: We have successfully calculated grades based on percentages using Excel’s “Nested IF” function.

Eg 6 Step 10-2

Things to Remember

The below table offers helpful tips and examples for using formulas to calculate grades in Excel.

Tips

Example

Enclose all text values with double quotes. “Olivia”, “English”
Numerical values need not be enclosed with double quotes. 50, 2.5
Use IF & AND conditions to check if all conditions are true.

For instance, use this formula to check if a student scored above 80 marks in all subjects.

IF(AND(A1>=80, B1>=80, C1>=80, D1>=80), “Excellent”, “Satisfactory”)
Use IF & OR conditions to check if any one condition is true.

For instance, to check if a student scored above 80 marks in any one subject, use this formula:

IF(OR(A1>=80, B1>=80, C1>=80, D1>=80), “Yes”, “No”)
Close formula with brackets equal to the number of IF statements.

If the formula has two nested IFs, it should be closed with two closing brackets like this:

=IF(A1>=85, “A”, IF(A1>=75, “B”, “C”))
When using the “VLOOKUP” function, the marks/score table column should be in ascending order, or the function can give an error or wrong result. –
In the last argument of the VLOOKUP function, use “TRUE” or 1 for an approximate match. VLOOKUP(B2,C2:E10,2,TRUE)

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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. Excel Match Function
  2. Cheat Sheet of Excel Formulas
  3. Remove Duplicates in Excel
  4. Scrollbar in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Formula for Grade Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Formula for Grade Excel Template

EDUCBA

डाउनलोड Formula for Grade Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW