Use The SWITCH Function to Categorize Data Efficiently

The SWITCH function is a useful tool for categorizing data. In the right circumstances, it can save you from messy, nested IF functions.

The SWITCH function is used to test an expression against a list of cases. It returns a value when the expression is equal to one of the cases.

It has some similarities to the IFS function but differs because SWITCH tests for exact matching rather than whether a condition is true (e.g. X > Y). SWITCH also has a default option to return a value if no match is found.

Let’s see an example. Suppose we have this data set of student grades and we want to add context to each grade level.

SWITCH function with student grades

Use this SWITCH function to categorize these grades:

=SWITCH(B2,
"A","Top marks! Great job!",
"B","Keep up the good work",
"C","Could do better",
"Failed. Remidial study needed")

Let’s break it down:

B2 is the condition we’re going to test. It’s the grade letter from column B and it’s the input to our SWITCH function.

We check the value of B2 against the first case “A”. If they match (i.e. the grade in B2 was also “A”) then SWITCH returns the string “Top marks! Great job!”.

If the value from B2 doesn’t match “A”, we move on and test it against “B”  and if that fails, test against “C”.

If the condition doesn’t match any of the cases, the last string is returned: “Failed. Remedial study needed”. It’s our catch-all solution. Anything that’s not “A”, “B” and “C” will return this answer.

This is what the solution looks like:

Solution

Give it a try and SWITCH things up!

The FACT Function in Google Sheets (And Why A Shuffled Deck of Cards Is Unique)

Let’s start with a mind-blowing fact, and then use the FACT function in Google Sheets to explain it.

Pick up a standard 52 card deck and give it a good shuffle.

The order of cards in a shuffled deck will be unique.

One that has likely never been seen before in the history of the universe and will likely never be seen again.

I’ll let that sink in.

Isn’t that mind-blowing?

Especially when you picture all the crazy casinos in Las Vegas.

Let’s understand why, and in the process learn about the FACT function and basic combinatorics (the study of counting in mathematics).

Four Card Deck

To keep things simple, suppose you only have 4 cards in your deck, the four aces.

You can create this deck in Google Sheets with the CHAR function:

CHAR cards

The formulas to create these four cards are:

Ace of Clubs:

=CHAR(127185)

Ace of Spades:

=CHAR(127137)

Ace of Hearts:

=CHAR(127153)

Ace of Diamonds:

=CHAR(127169)

Let’s see how many different combinations exist with just these four cards.

Pick one of them to start. You have a choice of four cards at this stage.

Once you’ve chosen the first one, you have three cards left, so there are 3 possible options for the second card choice.

When you’ve picked that second card, you have two cards left. So you have a choice of two for the third card.

The final card is the last remaining one.

So you have 4 choices * 3 choices * 2 choices * 1 choice = 4 * 3 * 2 * 1 = 24

There are 24 permutations (variations) with just 4 cards!

Visually, we can show this in our Google Sheet by displaying all the different combinations with the card images from above:

Fact function card combinations in Google Sheets

(I’ve just shown the first 6 rows for brevity.)

You can see for example, when moving from row 1 to row 2, we swapped the position of the two red suits: the Ace of Hearts and the Ace of Diamonds.

Five Card Deck

This time there are 5 choices for the first card, then 4, then 3, then 2, and finally 1.

So the number of permutations is 5 * 4 * 3 * 2 * 1 = 120

Already a lot more! I have not drawn this out in a Google Sheet and leave that as an optional exercise for you if you wish.

The FACT function

The FACT function in Google Sheets (see documentation) is a math function that returns the factorial of a given number. The factorial is the product of that number with all the numbers lower than it down to 1.

In other words, exactly what we’ve done above in the above calculations.

Four:

The 4 card deck formula is:

=FACT(4)

which gives an answer of 24 permutations.

Five:

The 5 card deck formula is:

=FACT(5)

which gives an answer of 120 permutations.

Six:

A 6 card deck is:

=FACT(6)

which gives an answer of 720 permutations.

Twelve:

A 12 card deck has  479,001,600 different ways of being shuffled:

=FACT(12)

(You’re more likely to win the Powerball lottery at 1 in 292 million odds than to get two matching shuffled decks of cards, even with just 12 cards!)

Fifty Two:

Keep going up to a full deck of 52 cards with the formula and it’s a staggeringly large number.

=FACT(52)

Type it into Google Sheets and you’ll see an answer of 8.07E+67, which is 8 followed by 67 zeros!

(This number notation is called scientific notation, where huge numbers are rounded to the first few digits multiplied by a 10 to the power of some number, 67 in this case.)

This answer is more than the number of stars in the universe (about 10 followed by 21 zeros).

Put another way if all 6 billion humans on earth began shuffling cards at 1 deck per minute every day of the year for millions of years, we still wouldn’t even be close to finding all possible combinations.

Build Numbered Lists With The Amazing SEQUENCE Function

The SEQUENCE function is a useful function in Google Sheets. It’s a powerful way to generate numbered lists.

Previously, you had to resort to obscure array formulas like 

=ArrayFormula(row(1:5))

to get lists of numbers. Things got ugly fast if you wanted to customize these lists.

Thankfully today, we have the SEQUENCE function.

As arguments, you specify: 1) the number of rows, 2) the number of columns, 3) a start value, and 4) a step size.

Arguments 2, 3 and 4 are optional. However, if you want to set them you need to include the previous ones (e.g. if you want to set a step size in argument 4, then you need to set 1, 2 and 3 as well).

Keep this order in mind as you look through the examples below and you’ll soon understand how the function works.

1. Ascending list of numbers

=SEQUENCE(5)

=SEQUENCE(5)

2. Horizontal list of numbers

Set the row count to 1 and the column count to however many numbers you want e.g. 5:

=SEQUENCE(1,5)

=SEQUENCE(1,5)

3. Two-dimensional array of numbers

Set both row and number values:

=SEQUENCE(10,5)

=SEQUENCE(10,5)

4. Start from a specific value

Set the third argument to the value you want to start from e.g. 100:

=SEQUENCE(5,1,100)

=SEQUENCE(5,1,100)

5. Use a custom step

Set the fourth argument to the size of the step you want to use, e.g. 10:

=SEQUENCE(5,1,1,10)

=SEQUENCE(5,1,1,10)

6. Descending numbers

Set the fourth argument to -1 to count down:

=SEQUENCE(5,1,5,-1)

=SEQUENCE(5,1,5,-1)

7. Negative numbers

Set the start value to a negative number and/or count down with negative step:

=SEQUENCE(5,1,-1,-1)

=SEQUENCE(5,1,-1,-1)

8. Dates

Dates are stored as numbers in spreadsheets, so you can use them inside the SEQUENCE function. You need to format the column as dates:

=SEQUENCE(5,1,TODAY(),1)

=SEQUENCE(5,1,TODAY(),1)

9. Decimal numbers

Unfortunately you can’t set decimal counts directly inside the SEQUENCE function, so you have to combine with an Array Formula e.g.

=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )

=ArrayFormula( SEQUENCE(5,1,10,1) / 10 )

10. Constant numbers

You’re free to set the step value to 0 if you want an array of constant numbers:

=SEQUENCE(5,1,1,0)

11. Monthly sequences

Start with this formula in cell A1, which gives the numbers 1 to 12 in a column:

=SEQUENCE(12)

In the adjacent column, use this DATE function to create the first day of each month (formula needs to be copied down all 12 rows):

=DATE(2021,A1,1)

This can be turned into an Array Formula in the adjacent column, so that a single formula, in cell C1, outputs all 12 dates:

=ArrayFormula(DATE(2021,A1:A12,1))

Finally, the original SEQUENCE formula can be nested in place of the range reference, using this formula in cell D1:

=ArrayFormula(DATE(2021,SEQUENCE(12),1))

This single formula gives the output:

1/1/2021
2/1/2021
.
.
.
12/1/2021

It’s an elegant way to create a monthly list. It’s not dependent on any other input cells either (columns A, B, C are working columns in this example).

With this formula, you can easily change all the dates, e.g. to 2022.

Building in steps like this a great example of the Onion Method, which I advocate for complex formulas.

12. Text and Emoji sequences

You can use a clever trick to set the SEQUENCE output to a blank string using the TEXT function. Then you can append on a text value or an emoji or whatever string you want to create a text list.

For example, this repeats the name “Ben Collins” one hundred times in a column:

=ArrayFormula(TEXT(SEQUENCE(100,1,1,1),"")&"Ben Collins")

And, by using the CHAR function, you can also make emoji lists. For example, here’s a 10 by 10 grid of tacos:

=ArrayFormula(TEXT(SEQUENCE(10,10,1,1),"")&CHAR(127790))

Repeating List with SEQUENCE function

Have you got any examples of using the SEQUENCE function?

Unpivot In Google Sheets With Formulas (How To Turn Wide Data Into Tall Data)

Unpivot in Google Sheets is a method to turn “wide” tables into “tall” tables, which are more convenient for analysis.

Suppose we have a wide table like this:

Wide Data Table

Wide data like this is good for the Google Sheets chart tool but it’s not ideal for creating pivot tables or doing analysis. The main reason is that data is captured in the column headings, which prevents you using it in pivot tables for analyis.

So we want to transform this data — unpivot it — into the tall format that is the way databases store data:

Unpviot in Google Sheets

But how do we unpivot our data like that?

It turns out it’s quite hard.

It’s harder than going the other direction, turning tall data into wide data tables, which we can do with a pivot table.

This article looks at how to do it using formulas so if you’re ready for some complex formulas, let’s dive in…

Unpivot in Google Sheets

We’ll use the wide dataset shown in the first image at the top of this post.

The output of our formulas should look like the second image in this post.

In other words, we need to create 16 rows to account for the different pairings of Customer and Product, e.g. Customer 1 + Product 1, Customer 1 + Product 2, etc. all the way up to Customer 4 + Product 4.

Of course, we’ll employ the Onion Method to understand these formulas.

Template

Click here to open the Unpivot in Google Sheets template

Feel free to make your own copy (File > Make a copy…).

(If you can’t open the file, it’s likely because your G Suite account prohibits opening files from external sources. Talk to your G Suite administrator or try opening the file in an incognito browser.)

Step 1: Combine The Data

Use an array formula like this to combine the column headings (Customer 1, Customer 2, etc.) with the row headings (Product 1, Product 2, Product 3, etc.) and the data.

It’s crucial to add a special character between these sections of the dataset though, so we can split them up later on. I’ve used the fox emoji (because, why not?) but you can use whatever you like, provided it’s unique and doesn’t occur anywhere in the dataset.

=ArrayFormula(B1:E1&"🦊"&A2:A4&"🦊"&B2:E4)

The output of this formula is:

Unpivot Data In Google Sheets Step 1

Step 2: Flatten The Data

Before the introduction of the FLATTEN function, this step was much, much harder, involving lots of weird formulas.

Thankfully the FLATTEN function does away with all of that and simply stacks all of the columns in the range on top of each other. So in this example, our combined data turns into a single column.

=ArrayFormula(FLATTEN(B1:E1&"🦊"&A2:A4&"🦊"&B2:E4))

The result is:

Unpivot Data In Google Sheets Step 2

Step 3: Split The Data Into Columns

The final step is to split this new tall column into separate columns for each data type. You can see now why we needed to include the fox emoji so that we have a unique character to split the data on.

Wrap the formula from step 2 with the SPLIT function and set the delimiter to “🦊”:

=ArrayFormula(SPLIT(FLATTEN(B1:E1&"🦊"&A2:A4&"🦊"&B2:E4),"🦊"))

This splits the data into the tall data format we want. All that’s left is to add the correct column headings.

Unpivot Data In Google Sheets Step 3

Unpivot With Apps Script

You can also use Apps Script to unpivot data.

Have a look at the example sheet from the first answer of this Stack Overflow post.

Further Reading

For more information on the shape of datasets, have a read of Spreadsheet Thinking vs. Database Thinking.

IFS Function in Google Sheets

The IFS function in Google Sheets is used to test multiple conditions and outputs a value specified by the first test that evaluates to true.

It’s akin to a nested IF formula, although it’s not exactly the same. However, if you find yourself creating a nested IF formula then it’s probably easier to use this IFS function.

Example IFS Function in Google Sheets

Suppose you have a set of student exam scores and you want to assign different grades to the students:

Simple IFS Function In Google Sheets

In this scenario, you want to put students into three groups: i) those who score below 50 failed the exam, ii) those with scores between 50 and 79 passed the exam, and iii) students who scored 80 or above passed with distinction.

The IFS Function to do this is:

=IFS(B2<50,"Fail",B2<80,"Pass",B2>=80,"Pass with distinction")

The IFS function consists of pairs of arguments: a condition to test and a value.

If the conditional test is true, the value is displayed and the function stops. If not, and the test is false, the function tries the next test/value pair.

Consider row 2 of the example above, where Bob has a score of 70 in cell B2.

The first logical test and value pair is:

B2<50,"Fail"

The function takes the value of 70 in cell B2 and compares it to the value of 50 to see if it’s less. This is false, so the “Fail” output is not displayed.

Instead, the IFS function in Google Sheets moves to the second logical test and value pair:

B2<80,"Pass"

This time the logical test – is 70 less than 80? – evaluates to true, so the function displays “Pass”.

The IFS formula never reaches the third test/value pair.

The result of the IFS function in Google Sheets is an output that classifies the students:

IFS Function In Google Sheets Exam Grade

You could modify this IFS formula to assign grades “A”, “B”, “C” etc. instead, based on bands.

Note, it can also be done with a nested IF function. This gives the same result as the IFS function, but is more complex to understand:

=IF(B2<50,"Fail",IF(B2<80,"Pass","Pass with distinction"))

IFS Function in Google Sheets: Syntax

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

It takes a minimum of two arguments:

condition1

This is a logical test that evaluates to a TRUE or FALSE value.

For example A1 > 10 or ISBLANK(A1)

value1

If condition1 is TRUE, then the IFS function will output this value.

condition2, value2

These are optional pairs of logical tests and values. If condition 1 is FALSE, the IFS moves on to test condition 2, then 3, then 4 etc.

IFS Function Notes

  • The conditions and values always come in a pair, so the IFS always has an even number of arugments.
  • The function reads the test/value pairs from left to right. It always starts with the leftmost argument as the first logical test.
  • There is no default value to display should all the conditions fail. The IFS function will then output an #N/A! error.
  • However, you can create a default fallback value by adding a penultimate argument TRUE (the logical test) and some value (the fallback value). This is illustrated in the bank account example below.

IFS function template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings. If you click the link and open in an Incognito window you’ll be able to see it.

You can also read about it in the Google documentation.

IFS Function Account Balance Example

Suppose you have a list of corporate bank balances and a table of interest rates, and you want to add the correct rate against the account balance so you can calculate the interest.

Account IFS Function Example

One way to do this is to use this IFS function in cell B2:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,A2>=250000,1.75%)

The first test compares the value in cell A2 to see if it’s less than $50,001, which means the account balance is in the $0 – $50,000 tier, so the formula returns a value of 0.5%.

If this first test fails, it tries the second logical test. If that fails, it tries the third logical test, etc.

As it’s written above, the interest rates are hardcoded in the formula, which is generally bad practice (because it’s hard to make changes and easy to make mistakes).

A better solution is to link the output values to the interest rate table, as shown in this version of the formula:

=IFS(A2<50001,$E$2,A2<100001,$E$3,A2<250001,$E$4,A2>=250000,$E$5)

(Exercise for readers: take this a step further and link the bounds (50001, 100001 etc.) to the interest rate table instead of hard coding them.)

The output looks like this:

Account IFS Function Solution

Note: The final logical test can be replaced with the word TRUE, as the catch-all when none of the other conditions are met:

=IFS(A2<50001,0.5%,A2<100001,0.75%,A2<250001,1.25%,TRUE,1.75%)

Alternative Solution

Note that this account example can also be solved with the VLOOKUP function using a TRUE value as the final argument.

This account example is covered in Day 6 of my free Advanced Formulas 30 Day Challenge course.

You might also consider using the SWITCH function, which works for exact matches.

Advanced IFS Function in Google Sheets

This is somewhat contrived but will give you an idea of what’s possible with the IFS function.

In this scenario, imagine that two parties of buyers have viewed some properties.

The agent wants to record each party’s preferences. He remembers:

“Party 1 liked apartments under $200k”

“Party 2 liked houses or townhouses”

There are various ways you could solve this, including simply recording the preferences manually in a Sheet, but let’s see an IFS formula that does it automatically.

Here’s the data table:

Advanced IFS Formula Data

And here’s the formula in cell D2 that can be dragged down the column and across the row to fill in the peferences:

=IFS(ISBLANK($C3),"",AND(D$1="Party 1",$B3="Apartment",$C3<200000),"Yes", AND(D$1="Party 2", OR($B3="House",$B3="Townhouse")), "Yes",TRUE,"Not interested")

The test/value pairs are grouped as follows:

Test # Test In Formula Output
1 Is cell C3 blank? ISBLANK($C3) ""
2 Is it party 1 and Apartment and less than $200k? AND(D$1 = "Party 1",$B3 = "Apartment",$C3 < 200000) Yes
3 Is it party 2 and house or townhouse? AND(D$1 = "Party 2",OR($B3 = "House",$B3 = "Townhouse")) Yes
4 Catch all when conditions 1 – 3 not true TRUE Not interested

You’ll notice that test/value pairs 2 and 3 show how to use a nested AND function and a nested OR function to combine conditions.

The output of this formula is:

Advanced IFS Formula