Calculate the students grades from the percentage in Google sheet

Google Sheets is a versatile spreadsheet application that can compute grades for students based on their percentages using IF statements and logical operatorsIt comprises operators like AND, OR, and NOT.. The grade system formula is a nested IF in Google Sheets 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 grading criteria above.

Solution

To calculate the grade, we have to use the nested IF. 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 formula above 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 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 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 Google Sheet formulas for grades, we must bear the following considerations in mind:

• Pay attention to the logical structure of the nested IF statements. The conditions for grade determination are hierarchical, with each IF statement representing a specific grade range. Verify that the conditions do not overlap or conflict with each other. The nested IF statements should follow a clear and logical sequence to correctly assign grades based on the specified criteria.

• Ensure accurate cell references in the formula. In this case, “J2” represents a specific cell in the “Percentage” column for the first student. When copying the formula for other students, it’s crucial to check that the cell references are adjusted correctly. This ensures that each student’s percentage is considered accurately when calculating grades.