Are you tired of manually converting numerical values into alphabetical grades in Google Sheets? Look no further! In this article, we’ll show you how to use the SWITCH function to display values corresponding to other values, making your life easier.
The Magic of the SWITCH Function
The SWITCH function in Google Sheets is a powerful tool that allows you to convert data from one format to another. Whether you want to convert numerical grades to alphabetical grades or any other conversion, the SWITCH function has got you covered.
To start using the SWITCH function, follow these simple steps:
- Type “=SWITCH” or go to the “Insert” tab (or the “Functions” icon) -> “Function” -> “Logical” -> “SWITCH”.
- Enter the expression, case, and value manually or by cell reference.
- Include all the cases and values until you have covered all the corresponding values.
- Finally, include a “default value” if necessary.
- Press the “Enter” key.
The general syntax of the SWITCH function is as follows:
- Expression: This is the value tested by the formula. You can enter this value by cell reference, applying the function to multiple values.
- Case1: If the tested value matches this value, “case1,” the formula returns a value defined as “value1”. If the tested value is not equal to this value, the function continues to search for other cases (if specified) for the tested value.
- Value1: This is the value displayed in the cell when the tested value is equal to “case1”.
- Case2: Same as “case1”.
- Value2: Same as “value1”. This value should be associated with “case2”.
- Default (optional): This is optional. If this default value is set, it is returned by the formula when the formula fails to find a match between a tested value and each case. Otherwise, the function returns “#N/A”.
Note: You need to enter pairs of “case” and “value” until you have included all the pairs. The mentioned values can be text, numbers, dates, etc.
Let’s assume you are a group manager and you use a numerical grade (“Score”) to evaluate the members of your group. However, due to a policy change in your company, you need to switch from the numerical grading system to an alphabetical grading system (“Grade”). You have defined the following relationships between scores and grades: “1” = “E,” “2” = “D,” “3” = “C,” “4” = “B,” and “5” = “A”. To illustrate these relationships, you have organized a table showing their correspondences. Here are a few examples.
Example 1 – Manual Entry Version
This example demonstrates what the formula looks like if you use manual entry.
The arguments in the SWITCH function in cell D4 are as follows:
- Expression: C4
- Case1: “1”
- Value1: “E”
- Case2: “2”
- Value2: “D”
- Case3: “3”…
- Default (optional): Not defined in this formula
The formula in cell D4 tests the value in cell C4, which is “5”, and returns “A” because the tested value matches “case5,” which is “5”. Therefore, the “value5,” which is “A,” is displayed in the cell. However, since Amelia and Lucas have scores that are not defined in the formula, the formulas return “#N/A”.
Example 2 – Cell Reference Version
The following example shows the same results, but all the formulas are entered by cell reference, which is highly recommended in terms of accuracy and convenience.
The arguments in the SWITCH function in cell D16 are as follows:
- Expression: C16
- Case1: $K$16
- Value1: $L$16
- Case2: $K$17
- Value2: $L$17
- Case3: $K$18…
- Default (optional): Not defined in this formula
In this example, the table describing the relationships between letters of the alphabet and numbers is referenced in the formula to simplify the process.
Example 3 – Cell Reference Version with Default Value
The last example shows what happens when the default value is included in the SWITCH formula. You decide that if a score is equal to or greater than 1, you will assign an “A+” grade to scores that are not linked to specific letters, such as “7” and “8,” which are Amelia and Lucas’s scores, respectively.
Amelia and Lucas’s grades, displayed as “#N/A” in the previous two examples, now appear correctly as “A+”. Here’s how the “default” argument works. The “default” argument must be included in the formula as the last entry, without being associated with a value. Otherwise, the formula recognizes them as a “case” and “value” pair.
Swap It Like It’s Hot: Exchanging Columns in Google Sheets
Do you need to swap columns in Google Sheets? We’ve got you covered! Check out this article to learn how to effortlessly swap two columns in Google Sheets.
Too Cool for Cell Swapping, but There’s a Way!
Unfortunately, you can’t directly swap two cells in Google Sheets like you can in Excel. But fear not! Rather than swapping individual cells, consider swapping two columns instead. It’s just as effective and will get the job done.
Transpose Like a Pro: Swapping Rows and Columns in Google Sheets
Do you want to transpose rows and columns in Google Sheets? Look no further! Check out this article to learn how to transpose rows and columns using the “Paste Special” feature or the TRANSPOSE function in Google Sheets.
For more information on using Google Sheets functions, visit Crawlan.com.
