Microsoft Excel is a powerful tool that can be used to compute grades for students based on their percentages using
Let’s first discuss the standards that will be utilized to determine each student’s grade based on the percentages they achieve.
Grade | Percentages |
A | 90% - 100% |
A- | 86% - 89% |
B+ | 81% - 85% |
B | 77% - 80% |
B- | 72% - 76% |
C+ | 68% - 71% |
C | 63% - 67% |
C- | 58% - 62% |
D+ | 54% - 57% |
D | 50% - 53% |
F | Below 50% |
Let’s assume we have a dataset of student information with the following data:
Subject marks
Marks obtained
Total marks
Percentages
We want to calculate the grade of every individual student using the above grading criteria.
To calculate the grade, we have to use the nested IF. Now, we will name the column beside the “Percentage” column “Grade.” This column will contain every student’s grade.
In the first cell of the “Grade” column, write the following formula that checks all the conditions for calculating the grade.
=IF(J2>=90,"A",IF(J2>=86,"A-",IF(J2>=81,"B+",IF(J2>=77,"B",IF(J2>=72,"B-",IF(J2>=68,"C+",IF(J2>=63,"C",IF(J2>=58,"C-",IF(J2>=54,"D+",IF(J2>=50,"D","F"))))))))))
Here, "J2"
represents a specific cell of the "Percentage"
column in MS Excel. "J"
refers to the column letter, indicating that it's in column J. "2"
refers to the row number, indicating that it’s in row 2. Now, let’s break down the formula above:
IF(J2>=90,"A",IF(J2>=86,"A-"))
The logic we used in the above formula is that if the student’s percentage is greater than or equal to 90%, then the student will get the grade "IF
. Let’s discuss it now:
IF(J2>=50,"D","F")
The logic in the formula above is that if the student’s percentage is greater than or equal to
Once we’ve determined the grade for the first student, click on the bottom-right corner of the cell containing that grade (where "
Now, drag this cursor downwards until we reach student number 11. The grades will be automatically calculated for the remaining students.
Remember that this is a general guideline, and grading practices can vary. Always refer to the specific grading policy for the most accurate information.
While utilizing MS Excel formulas for grades, we must bear the following considerations in mind:
The formula must be closed with the same number of brackets as the number of IF statements that were used within.
Declaring percent in the logical function is also necessary if we are working with percentages instead of numbers.
Follow a logical order in nested IF statements to evaluate conditions and assign grades appropriately.
Test your knowledge
Which Microsoft Excel function is used to determine the grade for each student according to their percentage score?
SUM
AVERAGE
IF
MAX