What Is Google Sheets Formula For Grade?
The Google Sheets formula for grade is a nested IF()-based formula that verifies a set of criteria. It then returns the corresponding grade as the output.
Users can utilize the Google Sheets formula for grade when they must assign grades to employees based on their annual salaries. The function is also helpful for evaluating the grade of the quality of service offered by customer service centers.
For example, the source dataset shows the monthly inventory levels as percentages at a store. Also, we have a table that shows the grades to assign based on the different inventory level percentage ranges.
The requirement is to assign the grades based on the inventory level percentages for all the months. We shall consider column F cells F2:F13 as the target cells.
Then, in line with the definition of the Google Sheets formula for grade explained before, we can apply a nested IF() formula, which is similar to Excel IF function, in the target cells to secure the desired outcome.
Let us now see the grade calculation logic based on which the Google Sheets formula for grade returns the required output in each target cell.
The outermost IF() in the nested IF formula checks if the inventory level percentage in the corresponding column E cell exceeds 80%. If the condition is true, the formula returns the first grade cited in the formula as the outermost IF()’s true value.
However, if the condition is not true, the next inner IF() gets executed.
Likewise, the nested IF() keeps checking the specified inventory level percentage for its range. So, once the formula gets the matching range, the Google Sheets formula for grade returns the corresponding grade, cited as the specific IF() true value, and the calculation stops.
Table of contents
Key Takeaways
- The Google Sheets formula for grade is a nested IF() formula that checks a set of conditions to determine the grade corresponding to the cited value.
- The formula to find the grade in Google Sheets helps determine an employee’s grade based on their overall years of experience. The formula is also useful for evaluating the customer service quality grades for financial institutions and the product quality grades at a store.
- We can use other inbuilt Google Sheets functions, such as VLOOKUP, LOOKUP, IFS, and SWITCH, to perform the required grade evaluation seamlessly.
Explanation And Uses
Explanation
Grades are an integral part of academic performance assessment and evaluating success in various other domains, such as Finance. However, when we calculate the grades manually, it can be a tedious process, and there can be chances of human error.
However, when we implement Google Sheets formula for grade, it ensures that we evaluate the grades with accuracy and quickly.
The first step is to gather all the source data in a Google Sheets file, which is crucial for determining the grades. Once we have this data arranged in a meaningful way, we can implement Google Sheets formula for grade.
For that, we must have the appropriate grading scale, which shows the corresponding value or range of values each grade represents. Next, we can now build a Google Sheets formula that automatically evaluates the grades according to the entered values.
Uses
The nested IF() implemented, in line with the definition of Google Sheets formula for grade explained earlier, finds applications in domains such as Education, Finance, and Marketing.
- The formula is useful for determining the grades of students at an educational institution based on their aggregates.
- The formula is useful for calculating the grades of employees at a firm based on their annual salaries.
- The formula helps calculate the product quality grade at a store.
- The formula helps calculate the grade of quality the service centers or financial institutions offer.
How To Use Google Sheets Formula For Grade Calculation?
The steps to use the formula for grade calculation in Google Sheets are as follows:
- Create a grading scale dataset that shows the corresponding value or range of values each grade denotes.
- Choose a target cell where we aim to display the grade.
- Enter the following nested IF() formula to determine the required grade.
=IF(condition1,grade1,IF(condition2,grade2,IF(condition3,grade3,…..,IF(conditionN,gradeN,gradeN+1))))
The Google Sheets formula for grade arguments explanation is as follows:
- condition1…conditionN: The conditions the nested IF() checks to determine the grade. Once a condition is satisfied, the formula returns the corresponding grade.
- grade1…gradeN+1: The grade the function must return according to the condition that is met.
- Press Enter to execute the grade calculation formula in Google Sheets.
Examples
The illustrations below show the different ways of using the formula for grade evaluation in Google Sheets.
Example #1 – Calculating The Grade Of Employees Using Google Sheets Formula
The source dataset lists employees and their annual salaries. Next, the dataset in columns A:B shows the annual pay scale bands and their corresponding pay grades.
We must update the pay grades for the employees listed in the source dataset based on their annual salaries in column F.
Step 1: Select cell F2, enter the nested IF() formula, and press Enter.
=IF(E2>1000000,”A”,IF(AND(E2>80000,E2<=1000000),”B”,IF(AND(E2>60000,E2<=800000),”C”,IF(AND(E2>40000,E2<=60000),”D”,IF(AND(E2>20000,E2<=40000),”E”,”F”)))))
Step 2: Utilize the fill handle, which is similar Excel fill handle, to feed the formula into the rest of the target cells.
Let us check the cell F7 formula, based on the Google Sheets formula for grade arguments, to understand the logic.
The outermost IF() checks if the annual salary of 15000, cited in cell E7, exceeds 1000000. Since the condition is not true, the outermost IF() returns the false value.
So, the first inner IF() executes. The AND(), which works like the Excel AND function, in its logical_expression argument checks if the annual salary of 15000, cited in cell E7, exceeds 80000 and is below or equal to 1000000. Since the two conditions in the AND() are not true, the first inner IF() returns the false value.
So, the second inner IF() executes. The AND() in its logical_expression argument checks if the annual salary of 15000, cited in cell E7, exceeds 60000 and is below or equal to 80000. Since the two conditions in the AND() are not true, the second inner IF() returns the false value.
So, the third inner IF() executes. The AND(), in its logical_expression argument, checks if the annual salary of 15000, cited in cell E7, exceeds 40000 and is below or equal to 60000. Since the two conditions in the AND() are not true, the third inner IF() returns the false value.
So, the fourth inner IF() executes. The AND() in its logical_expression argument checks if the annual salary of 15000, cited in cell E7, exceeds 20000 and is below or equal to 40000. Since the two conditions in the AND() are not true, the fourth inner IF() returns the false value, which is the grade F.
Please note that the grades cited in the nested IF() formula are according to the grading scale dataset mentioned in column A:B.
Example #2 – Calculating The Product Quality Grade Using Google Sheets Formula For Grade
The source dataset lists ceramic product IDs and the number of defects they have. Next, the grading scale dataset in columns A:B shows the different ranges of total defects and their corresponding quality grades.
The requirement is to update the quality grades for the ceramic products listed in the source dataset based on the number of defects they have endured. Assume cells F2:F7 are the target cells.
Step 1: Select cell F2, enter the nested IF() formula, and press Enter.
=IF(E2=0,”A”,IF(AND(E2>=1,E2<=10),”B”,IF(AND(E2>=11,E2<=20),”C”,”D”)))
Step 2: Utilize the fill handle to implement the expression in the rest of the target cells.
Let us check the cell F7 formula to know the logic.
The outermost IF() checks if the number of defects, 18, cited in cell E7, equals 0. Since the IF() condition is not true, the outermost IF() returns the false value.
So, the first inner IF() executes. The AND() in its logical_expression argument verifies if the number of defects, 18, cited in cell E7, exceeds or equals 1 and is less than or equal to 10. Since the two conditions in the AND() are not true, the first inner IF() returns the false value.
So, the second inner IF() executes. The AND() in its logical_expression argument verifies if the number of defects, 18, cited in cell E7, exceeds or equals 11 and is less than or equal to 20. Since the two conditions in the AND() are true, the second inner IF() returns the true value, which is the grade C as the required quality grade.
Please note that the grades cited in the nested IF() formula are in line with the grading scale dataset provided in column A:B.
Example #3 – Calculating The Grade Of Quality Offered By Financial Institutions Using Google Sheets Formula
The source dataset contains a list of financial institutions and their customer service quality ratings. Next, the grading scale dataset in columns A:B lists the different customer service quality ratings and their corresponding service quality grades.
The aim is to update the service quality grades for the financial institutions listed in the source dataset based on their customer service quality ratings. Assume cells F2:F5 are the target cells.
Step 1: Select cell F2, enter the nested IF() formula, and press Enter.
=IF(E2=5,”A”,IF(E2=4,”B”,IF(E2=3,”C”,IF(E2=2,”D”,E))))
Step 2: Utilize the fill handle to implement the expression in the rest of the target cells.
Let us check the cell F5 formula to understand the logic.
The outermost IF() checks if the customer service quality rating, 3, cited in cell E5, equals 5. Since the IF() condition is not true, the outermost IF() returns the false value.
So, the first inner IF() executes. It checks if the customer service quality rating, 3, cited in cell E5, equals 4. Since the IF() condition is not true, the first inner IF() returns the false value.
So, the second inner IF() executes. It checks if the customer service quality rating, 3, cited in cell E5, equals 3. Since the IF() condition is true, the second inner IF() returns the true value, which is the grade C.
Please note that the grades cited in the nested IF() formula adhere to the grading scale dataset provided in column A:B.
Important Things To Note
- Ensure the grading scale dataset maps the values to the corresponding grades correctly. It should also cover all the possible values. Otherwise, the Google Sheets formula for grade determination will return an incorrect value or error value.
- If the given values to check for grades are percentages instead of numbers, we must mention the “%” symbol in the logical formula to determine grades in Google Sheets. However, in typical cases, we need to mention only the numbers. Otherwise, the formula output can be an error.
Frequently Asked Questions (FAQs)
We can calculate letter grades based on score values with the VLOOKUP function in Google Sheets using the following method, explained with an example.
The source dataset contains a list of students and their overall scores. Next, the grading scale dataset in columns A:B lists a set of overall scores and their corresponding grades.
The aim is to update the grades for the student listed in the source dataset based on their overall scores. Assume cells F2:F9 are the target cells.
Step 1: Select cell F2, enter the nested IF() formula, and press Enter.
=VLOOKUP(E2,$A$2:$B$7,2,TRUE)
Step 2: Utilize the fill handle to implement the expression in the rest of the target cells.
The VLOOKUP() accepts four inputs.
The first is the search key, cited in the corresponding column E cell. Next, the second argument is the search range A2:B7, which contains the grading scale dataset. Next, as we must retrieve the grade from the second column of the search range, the third argument value is 2. Finally, the last argument is TRUE, which indicates that the function can find an approximate match for the search key, which can be equal to or less than the cited search key.
Thus, for the furnished inputs, the VLOOKUP() returns the grade from the grading scale dataset, corresponding to the cited overall score of the concerned student.
The other Google Sheets functions we can use to determine grades are the LOOKUP(), IFS(), and SWITCH().
You can prefer to use a different grade system, such as a percentage scale in Google Sheets.
For that, you must create a dataset that maps the percentage ranges to their respective grades. Next, apply the grade evaluation nested IF() formula in Google Sheets using the percentage values in the IF() conditions to make the formula return the corresponding grade.
Download Template
This article must be helpful to understand the Google Sheets Formula For Grade, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Google Sheets Formula For Grade. We learn how to use Google Sheets formula for grade with examples & points to remember. You can learn more from the following articles –
Leave a Reply