Display 5-Level Grades Based on Scores in Excel
If you want to automatically assign a grade based on a score in Excel, the IF function or SWITCH function can help. This guide explains how to set up grading where scores of 90 and above receive an “A,” 80 and above receive a “B,” 70 and above a “C,” 60 and above a “D,” and 50 and above an “E.”
Using the IF Function to Assign Grades
The IF function can be nested to evaluate score ranges and assign grades accordingly. Here’s how you can do it.
Steps
- Prepare data with names in column A and scores in column B.
- In column C, where grades will appear, enter the following formula:
Example Formula
Enter the following formula in cell C2 to assign a grade based on the score in B2:
=IF(B2>=90, “A”, IF(B2>=80, “B”, IF(B2>=70, “C”, IF(B2>=60, “D”, IF(B2>=50, “E”, “”)))))
Formula Breakdown
- IF(B2>=90, “A”, …): If the score in B2 is 90 or above, display “A.”
- IF(B2>=80, “B”, …): If the score is 80 or above but less than 90, display “B.”
- IF(B2>=70, “C”, …): If the score is 70 or above but less than 80, display “C.”
- IF(B2>=60, “D”, …): If the score is 60 or above but less than 70, display “D.”
- IF(B2>=50, “E”, “”)): If the score is 50 or above but less than 60, display “E.” If it’s below 50, leave the cell blank (“”).
Example Using IF Function
Here is an example table where column A contains names, column B contains scores, and column C displays grades:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Score | Grade |
| 2 | John | 95 | =IF(B2>=90, “A”, IF(B2>=80, “B”, IF(B2>=70, “C”, IF(B2>=60, “D”, IF(B2>=50, “E”, “”))))) |
| 3 | Tom | 82 | =IF(B3>=90, “A”, IF(B3>=80, “B”, IF(B3>=70, “C”, IF(B3>=60, “D”, IF(B3>=50, “E”, “”))))) |
| 4 | Jack | 68 | =IF(B4>=90, “A”, IF(B4>=80, “B”, IF(B4>=70, “C”, IF(B4>=60, “D”, IF(B4>=50, “E”, “”))))) |
| 5 | Joe | 59 | =IF(B5>=90, “A”, IF(B5>=80, “B”, IF(B5>=70, “C”, IF(B5>=60, “D”, IF(B5>=50, “E”, “”))))) |
Using the SWITCH Function for Grading
The SWITCH function, available in Excel 2019 and later, provides a simpler way to handle multiple conditions. This example uses SWITCH to assign grades based on score ranges.
Example Formula
Enter the following formula in cell C2 to assign grades based on the score in B2:
=SWITCH(TRUE, B2>=90, “A”, B2>=80, “B”, B2>=70, “C”, B2>=60, “D”, B2>=50, “E”, “”)
Formula Breakdown
- SWITCH(TRUE, condition1, result1, condition2, result2, …): Evaluates each condition. If it is TRUE, returns the corresponding result.
- B2>=90, “A”: If the score is 90 or above, return “A.”
- B2>=80, “B”: If the score is 80 or above but less than 90, return “B.”
- B2>=70, “C”: If the score is 70 or above but less than 80, return “C.”
- B2>=60, “D”: If the score is 60 or above but less than 70, return “D.”
- B2>=50, “E”: If the score is 50 or above but less than 60, return “E.”
- “”: If none of the conditions are met, return an empty cell.
Conclusion
Whether you use the IF function or the SWITCH function, you can easily create a system to assign grades based on scores in Excel. The SWITCH function simplifies handling multiple conditions and is especially useful for complex grading systems. Both methods are excellent tools for creating student reports, performance evaluations, or any other scoring system. Try them out!