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"
  }
}

Paste the above syntax, and hit the Save button. Then reload your spreadsheet.

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

Converting a percentage to a grade is a common task in Google Sheets for many professionals.

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!