Calculate the students grades from the percentage in MS Excel

Microsoft Excel is a powerful tool that can be used to compute grades for students based on their percentages using IF statementsThe IF statement allows a block of code to be executed if the condition provided is met. and logical operatorsIt comprises operators like AND, OR, and NOT.. The grade system formula is a nested IF in MS Excel that checks for specific conditions and returns the specified grade if satisfied. Therefore, the formula we will use to determine the grade must be created such that it reviews all of the requirements we have for evaluating the grade slab and provides the grade that is appropriate for the situation.

Let’s first discuss the standards that will be utilized to determine each student’s grade based on the percentages they achieve.

Let’s assume we have a dataset of student information with the following data:

• Subject marks

• Marks obtained

• Total marks

• Percentages

Problem statement

We want to calculate the grade of every individual student using the above grading criteria.

Solution

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.

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:

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 "$A$". If the first condition, i.e. $(J2>=90)$ is not true, it then checks for the second condition, i.e. $(J2>=86 )$ if the student’s percentage is greater than or equal to 86%, then the student will get a grade "$A-$". The rest of the nested IFs in the formula are all the same except for the last IF. Let’s discuss it now:

The logic in the formula above is that if the student’s percentage is greater than or equal to $50$, then the student will get grade "$D$" and if this condition is false, then the student will get grade "$F$".

Once we’ve determined the grade for the first student, click on the bottom-right corner of the cell containing that grade (where "$A-$" is displayed). We will notice a cursor with a plus sign.

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.

Key points

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.