Do you need to calculate a letter grade from a percent? This post is going to show you how to turn a percent score into a letter grade in Google Sheets.

While grading is often associated with teachers and professors, they’re not the only ones who use grades to identify similar measures. In many situations, people from other professions also use grading systems.

A grade is simply a label used to identify a group of objects that share similar characteristics. Whether it is student scores or product categories, you’re grading as long as you’re grouping based on shared similarities and giving them a label.

Putting things in a group manually is daunting. But you wouldn’t have to do that after reading this article because you will learn simple and easy ways spreadsheet methods that you can use to grade items in a few clicks.

The methods that will be discussed for calculating grades will involve the use of the following functions and features.

• IF function,
• VLOOKUP function,
• MATCH & CHOOSE functions,
• IFS function,
• SWITCH, and
• Custom apps script function.

Get your copy of the example workbook and follow along with this post to learn how to calculate a letter grade from a percent score.

## Calculate Grade with the IF Function

The popular IF function finds use in many situations particularly because it is flexible and easily adaptable.

``= IF ( logical_expression, value_if_true, value_if_false )``

The IF function is one of the spreadsheet’s logical functions. Instead of returning a true or false, you can test an expression and specify what values to return for a true or false output.

To perform its operations, the IF function uses three arguments.

• `logical_expression` is where you specify the condition that determines what values the IF function will return.
• `value_if_true` is where you define what to return if the `logical_expression` is true.
• `value_if_false` is where you define what to return if the `logical_expression` is false.

You can use other functions in any of these arguments. This way you can use multiple IF functions to create `if... then... else if...` logic.

Say we want to assign grades to the scores in this sample. Here’s how to do it with the IF function.

``= IF ( C3>=90%, "A+", IF ( C3>=80%, "A", IF ( C3>=70%, "B", IF ( C3>=60%, "C", IF ( C3>=50%, "D", "F" ) ) ) ) )``

The grades will be assigned when you copy and paste the IF syntax. This is a long IF statement and might look a little difficult to understand, but when you get the logic, it’s easy.

Notice there are IF statements inside of another IF statement? This is an example of a nested IF statement.

In the above syntax, the first IF statement tests whether the value in cell C3 is greater than or equal to 90%. If this is true, the IF returns a grade of A+. The other IF functions in the syntax don’t get executed because there’s no need to since the condition has been met.

Since the value in cell C3 doesn’t meet the condition, that means the `logical_expression` argument is false. This means the syntax will proceed to the `value_if_false` section of the argument and try to return what value is specified there.

This is where things might get a little tricky because instead of using a value, you have another IF function. Each argument can take other functions.

The first IF function takes care of values greater than or equal to 90%. But, because we have five grade categories, the nested IF‘s become necessary to cover every category.

That is why the `value_if_false` argument in the first IF statement takes another IF statement in its argument. This second IF statement then tests if the value in cell C3 is greater than or equal to 80% since it is not greater than or equal to 90%.

If the `logical_expression` argument for the second IF statement is true, it returns A and passes it into the `value_if_false` argument of the first IF statement which is then returned as output in the cell. And this same logical process guides the operation of the other nested IF statements.

The other IF statements test a condition and determine a value for the `value_if_false` argument in the first IF statement.

You can tell that having too many nested IF statements can cause the syntax to break down if the logic is not properly stated. This is the major disadvantage of using this method. Nevertheless, when you take your time to understand how it works, you will find it very easy to use.

## Calculate Grade with the VLOOKUP Function

VLOOKUP is another function that is perfectly suited for calculating the grade. Compared to the IF function, the VLOOKUP is much easier to use.

``= VLOOKUP ( search_key, range, index, [is_sorted] )``

VLOOKUP allows you to get data from another table using a search key. VLOOKUP runs a search down the first column of the table based on the search key and returns the associated value in another column specified in the lookup syntax.

VLOOKUP has four arguments.

• `search_key` is the value you are looking for in a table.
• `range` is the table containing the data where you are conducting the search.
• `index` references the column you want to return. The column `index` starts from 1.
• `is_sorted` is an optional argument that determines the type of lookup performed.
• TRUE will return an approximate match. This is the default when the argument is omitted.
• FALSE will return an exact match.

When set to FALSE, the VLOOKUP returns an exact match. If there are multiple values, it returns the value of the first matched cell.

When set to TRUE, the VLOOKUP returns the nearest match of the search key. This means it returns a value that is either equal to or less than the search key.

For the VLOOKUP to return appropriate data, the values in the first column of the search table must be sorted in ascending order.

To use the VLOOKUP function to calculate a grade, you will need to create a new table that contains the grades scores and their corresponding grade label.

📝 Note: The first column should contain the percent scores as this will be the search key used by VLOOKUP. This column also needs to be sorted in ascending order.

``= VLOOKUP ( C12, \$B\$3:\$C\$8, 2, true )``

The VLOOKUP syntax uses the values in the Score column as the search key. It searches for these values in the Percent column from the first table and uses this to return their corresponding grade.

Since the values aren’t exact matches, the syntax returns the values that are less than or equal to the search key. This is because the syntax uses TRUE in the `is_sorted` argument.

``= VLOOKUP ( C12, \$B\$3:\$C\$8, 2, false )``

If a FALSE argument was used to search for the value in cell C12 for instance, it will return an error because an exact equivalent of the search key, 63%, doesn’t exist in the second Percent column.

Sorting the search key column is essential to the workings of the VLOOKUP with an approximate match.

``= VLOOKUP ( C13, \$B\$3:\$C\$8, 2, true )``

When the search key in the search table isn’t sorted as shown above here, the VLOOKUP will return the wrong search value.

📝 Note: The VLOOKUP can only search through the first column in a table. It can only search from left to right.

``= VLOOKUP ( C13, \$B\$3:\$C\$8, 1, true )``

In this illustration, the Grade column is now the first column, and the syntax tries to search for the Percent values which are on the right. Even though the search key which is the values in the Percent column is sorted, the VLOOKUP returns an #N/A error because it can only search from left to right.

So, as far as the VLOOKUP is concerned, the search key value doesn’t exist because it’s not the first column in the search table.

The VLOOKUP function is a much easier route to calculating grades when compared to the IF function. The syntax is simple, easy to understand, and short. Once you get a handle on the little details like sorting, placing the search key values in the first column, and understanding the role of the TRUE/FALSE values in the is_sorted argument, you should have no challenges using the VLOOKUP.

## Calculate Grade with the MATCH & CHOOSE Functions

The other methods have used just one function to calculate the grade. This one involves the use of two powerful and interesting spreadsheet functions, the MATCH and CHOOSE functions.

### MATCH Function

MATCH is another variation of the lookup functions in the spreadsheet. It searches for a value and returns the position of the value from a range.

``= MATCH ( search_key, range, [search_type] )``

The MATCH function has three arguments.

• `search_key` is the value you are searching for.
• `range` is the location you are searching to find a match. This must be one-dimensional, which means it must be 1 row or 1 column. The range to be searched in MATCH will return an #N/A error when this rule is violated.
• `search_type` is optional and determines how the MATCH function will return values.
• 1 returns the largest value that is less than or equal to the `search_key`. This should be used when the `range` is sorted in ascending order.
• 0 returns the exact value for the `search_key`. It should be used when the range is not sorted.
• -1 returns the smallest value greater than or equal to the `search_key`. It should be used when the range is sorted in descending order.

### CHOOSE Function

The CHOOSE function simply returns values from a list provided within the function using an index.

``= CHOOSE ( index, choice1, [choice2, …] )``

Arguments in the CHOOSE function are index and choice.

With the CHOOSE function, you enter a list of items using the `choice` arguments.

Each choice is indexed in ascending order, and you can make a list of up to 30 choices.

The first item in the choice argument carries an index of 1, the second carries an index of 2, and so on until it reaches the last item in the choice argument.

To return a specific item from the list, you simply provide the index of that item in the `index` argument.

### Calculate Grade with MATCH & CHOOSE Example

``= CHOOSE ( MATCH ( C3, \$B\$12:\$B\$17, 1 ), "F", "D", "C", "B", "A", "A+" )``

You can calculate grade using the CHOOSE and MATCH functions in this syntax.

Taking the syntax from the inside out, the MATCH function searches for the value in cell C3 from the range B12:B17, returning the largest value greater than or equal to the value in cell C3 because the `search_type` argument is set to 1.

This is appropriate because the values in the one-dimensional Percent table are sorted in ascending order.

In this case, the value in cell C3 is 63%, so the value that will be matched from the second table is 60% since it is the largest value that is less than or equal to 63%.

For cell C3, the MATCH function will return 3 because 60% is in row 3. This value will then be passed to the `index` argument in the CHOOSE function.

The items in the choices argument of CHOOSE function have been arranged to follow the order with which the values in the Percent column are sorted.

This is important so the correct grade is returned, it is also why the Grade column is important as it can serve as a guide on how to arrange the choice list in the CHOOSE function.

Since the `index` value is 3, this matches grade C which is the third choice in the choice argument. The CHOOSE function returns C for a grade of 63%.

When you copy this formula across the remaining cells, the syntax will return the correct grades following the same process.

📝 Note: You must ensure that your column is sorted in ascending order to return the right result.

``= CHOOSE ( MATCH ( C3, \$B\$12:\$B\$17, -1 ), "A+", "A", "B", "C", "D", "F" )``

If the Percent column is sorted in descending order and the syntax is altered to reflect this change, you can see that the results returned are completely different and not appropriate for the desired outcome.

## Calculate Grade with the SWITCH Function

SWITCH is another function that you can use to easily calculate grades.

It uses the same logic as the nested IF‘s used earlier. The SWITCH function allows you to avoid nesting any other functions.

The SWITCH function switches values based on the evaluation of an expression. The expression is tested against a list of cases with corresponding return values that are defined in the SWITCH function.

When the expression returns a case that matches one on the list, then the value that corresponds to the matched case is returned.

``= SWITCH ( expression, case1, value1, [case2_or_default, …], [value2, …] )``

The arguments in the SWITCH function are as follows.

• `expression` is any valid value. It can be a hardcoded value or a function that returns a value.
• `case1` is the first case that is checked against the `expression`.
• `value1` is the value that should be returned when the `expression` matches `case1`.
• `case2`, `value2` are additional pairwise cases and values for when the first case doesn’t match the `expression`.
• `default` is an optional value that should return if the `expression` doesn’t match any case.
``= SWITCH ( true, C3>=90%, "A+", C3>=80%, "A", C3>=70%, "B", C3>=60%, "C", C3>=50%, "D", "F" )``

The `expression` argument in this syntax is set to true. This means the first case which evaluates to a true value will return its corresponding letter grade value.

In this example, the value in cell C3 is not greater than or equal to 90% so the test `C3>=90%` is false and the next case is evaluated.

This continues until it the condition `C3>=60%` is checked and returns a true value so the corresponding `"C"` value is returned.

You can see how the SWITCH function is an upgrade to the nested IFs method.

## Calculate Grade with the IFS Function

Another function that eliminates the use of the nested IF‘s method is the IFS function. This function was specifically designed to replace any need for nested IF‘s and it works a little like the SWITCH function.

``= IFS ( condition1, value1, [condition2, …], [value2, …] )``

The IFS function is very simple. You specify a criterion the value that should return when the criteria are met. It has two arguments.

• `condition` is the criteria you want to evaluate.
• `value` is the value that should return if the condition is true.

You can evaluate multiple criteria by adding more `condition` and `value` arguments. A `value` must be specified for every `condition`, these arguments must appear pairwise.

The IFS function is more robust when you want to test more than one condition.

``= IFS ( C3>=90%, "A+", C3>=80%, "A", C3>=70%, "B", C3>=60%, "C", C3>=50%, "D", C3>=0%, "F" )``

The above IFS syntax will return the letter grade for the percent score.

Each condition in the IFS function is tested until a true case is returned. Then the corresponding `value` for the `condition` that is true will be returned.

## Calculate Grade with an Apps Script

The methods described would require you to write long syntaxes which are sometimes complex anytime you want to calculate a grade.

If you are repeatedly calculating grades in Google Sheets, then you might want to create a more reusable solution.

With a custom apps script function, you only need to write the syntax once and you can reuse it anytime you need it. That’s the beauty of app scripts!

Go to the Extensions menu and click on the Apps Script option to open the apps script editor.

``````function GRADE(percent) {
if (percent >= 0.9 && percent <= 1) {
return "A+";
}
else if (percent >= 0.8 && percent <= 0.899) {
return "A";
}
else if (percent >= 0.7 && percent <= 0.799) {
return "B";
}
else if (percent >= 0.6 && percent <= 0.699) {
return "C";
}
else if (percent >= 0.5 && percent <= 0.599) {
return "D";
}
else if (percent <= 0.499 && percent >= 0) {
return "F";
}
else {
throw "score is out of range"
}
}
``````

When you reload the spreadsheet, the code will create a custom function named GRADE. The function uses `if... then... else if...` statements to test the values and return their corresponding grade.

You don’t have to worry about how the ifs work because you only need to provide a range for the custom function, and it returns the correct grade.

``= GRADE ( C3 )``

The above formula can now be used in your workbook to return the letter grade by referencing a single cell with the percentage score.

This massively simplifies the process of calculating a grade!

The script also throws an error for values that are out of range. You can place your mouse over the error to reveal the error message. It will also return an error if the values are not formatted as a percentage.

You can easily modify the script to accommodate numbers that are not percentages by changing the range values in the if syntax.

## Conclusions

You can now easily assign grades using any of the methods described. Functions such as IF, VLOOKUP, MATCH and CHOOSE, and SWITCH are all excellent ways to find the grade based on the percent.

If you are often required to convert percent to grade, then using the custom apps script function is the easiest and most repeatable way to go.

Are there any other methods you use for assigning grades? Let me know down in the comments! ## Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.