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!

## 0 Comments