Google Sheets and Apps Script Blog Posts From Ben Collins

Use The Onion Framework To Approach Complex Formulas

Complex Formulas? The Onion Framework? Huh?

I’m talking about the idea that complex formulas in Google Sheets are a lot like onions.

They have layers.

And they sometimes make you cry!

The Onion Method For Complex Formulas

If you’re building complex formulas, then I advocate following a one-action-per-step approach.

What I mean by this is that you build your formulas in a series of steps, and only make one change with each step.

The Onion Method is a framework by which to approach hard formulas, and consists of these three elements:

Put each new step of the formula in a new cell
Label each step with a simple “Step 1”, “Step 2”, etc. in adjacent cells
Change the background color of each formula cell, so they can be easily found

This lets you see the formula progress in an incremental way and is really helpful when you’re building or tyring to understand complex formulas.

Sometimes a step might result in an error (typically a #N/A or #REF!), but that’s ok, provided it gets fixed in a subsequent step, as shown in this SUMPRODUCT example:

Advanced Formula steps example

Each of these intermediary formulas in the above image moves us forward incrementally, until the final answer is obtained in step 6.

Similarly, if you’re trying to understand complex formulas, peel the layers back until you reach the core (which is hopefully a function you understand!). Then, build it back up in steps to get back to the full formula.

Example 1: Building Complex Formulas With The Onion Method

Let’s start with this job positions dataset and use the QUERY function to summarize the results:

Job position dataset

Step 1

Setup the first, simple QUERY formula to select columns A and B:

=QUERY(A1:B,"select A, B")

This doesn’t change the data, but it’s always a good idea to set up a basic query first to ensure you have the correct dataset selected as the input to your QUERY function.

Step 2

Summarize the data by job position, using a GROUP BY clause in the QUERY function:

=QUERY(A1:B,"select A, sum(B) group by A")

Onion Method for complex formulas

Step 3

Filter out the blank rows using the WHERE clause: “is not null”, as follows:

=QUERY(A1:B,"select A, sum(B) where A is not null group by A")

Step 4

Use an ORDER BY clause to sort the table by total in descending order:

=QUERY(A1:B,"select A, sum(B) where A is not null group by A order by sum(B) desc")

Step 5

Fix the header of the total column using the LABEL clause:

=QUERY(A1:B,"select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) 'Total Positions'")

Onion Method for complex formulas

Good work!

We’ve created a pivot table using the QUERY function rather than an actual pivot table. Building it in steps, where the formula evolves slightly with each step, was key to making this work.

Let’s continue, and see how to add a total row to this QUERY formula.

Step 6

Using array literals, add a placeholder line for the total row:

={QUERY(A1:B,"select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) 'Total Positions'");{"TOTAL","TBC"}}

Step 7

Our final step is to convert this placeholder to an actual formula, to give the correct total. As with the data input to the query function, we leave the range reference open-ended to ensure it remains dynamic and will include new data automatically:

={QUERY(A1:B,"select A, sum(B) where A is not null group by A order by sum(B) desc label sum(B) 'Total Positions'");{"TOTAL",SUM(B1:B)}}

The result is:

Onion Method for complex formulas

Example 2: Deconstructing Complex Formulas With The Onion Method

If you’re trying to understand complex formulas in Google Sheets that someone else has shared with you, you can still approach it with this Onion Method.

Simply peel back the layers until you reach the innermost function. Copy that into a new cell and start from the inside and work out, building up to the full formula again.

Let’s see an example.

Suppose we’re given this worksheet with US State names:

dataset of US State Names

And we’re also given this formula:

=ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))

which gives an output of Texas.

But how does this formula work?

Applying The Onion Method, we peel back the layers to the core function and then build it up in steps again.

Step 1

In a new cell, add the innermost MATCH function:

=MATCH(A1:A20,A1:A20,0)

Step 2

=ArrayFormula(MATCH(A1:A20,A1:A20,0))

which outputs an array of the position of the first occurrence of the words in column A. We see a 2 next to every occurrence of Texas for example, because the first time it occurred was in position 2.

Step 3

Now, we wrap it with the MODE function to find the most frequently occurring position:

=ArrayFormula(MODE(MATCH(A1:A20,A1:A20,0)))

By definition, the MODE function takes a range of numbers for input and finds the most commonly occurring value.

However, what happens if we have a range of text values and want to find the most frequent?

In this case, MATCH has been used to create a range of numbers for the MODE function.

By now, we’ve probably deduced that this formula finds the most frequent word in a list.

Step 4

Finally, we can retrieve the actual text value, i.e. the most frequent State name, by adding the INDEX function to get the full original formula, like this:

=ArrayFormula(INDEX(A1:A20,MODE(MATCH(A1:A20,A1:A20,0))))

This will give the output Texas in this specific example.

Nice!

Another Example To Deconstruct Complex Formula

Here’s another example of the Onion method to deconstruct complex formulas:

Get A Unique List Of Items From A Column With Grouped Words

Complex Formulas Onion Method Template For Your Use

Click here to open a read-only copy of the template >>

This template contains both examples from this tutorial.

To make your own editable copy, please go to File > Make a copy… under the File menu.

Complex Formulas Onion Method Conclusion

The Onion Method is a framework that allows you to approach complex formulas in a systematic way.

Even if you’re presented with an “impossible” challenge to answer or an “impossible” formula to decipher, just follow this framework. If required, peel back the layers and then work from the inside out in an incremental fashion.

You’ll be amazed at how quickly your understanding of challenging formulas broadens and deepens. You’ll encounter and understand brand new functions that you’ve never heard of before. Plus, you’ll find out all sorts of secret tricks with existing formulas.

Who knows, you might even cry tears of joy instead of despair…

Radial Bar Charts in Google Sheets

In this post, I’m going to show you how to create radial bar charts in Google Sheets.

Radial Bar Chart in Google Sheets

They look great and grab your attention, which is important in this era of information overload.

But they should be used sparingly because they’re harder to read than a regular bar chart (because it’s harder to compare the length of the curved bars).

How To Create A Radial Bar Chart In Google Sheets

Let’s begin with the data.

In this example, we’ll create a radial bar chart in Google Sheets with 3 series.

We need a column of values for these 3 series, for example, products with a number of units sold.

Next, we need some upper limit (max value) for our bars. This allows us to scale the bars properly.

Lastly, we need a helper column that calculates the difference between the max value and the actual value.

Here’s the data for the radial bar chart, in cells E3:H6:

Radial bar chart data table with Formula

Ok, I’m going to let you in on a little secret now…

This is not a single chart. No sir, it’s three charts overlaid on top of each other.

And yes, this means it takes three times as long to create!

Step 1: Create the inner circle

Highlight the first row of data but exclude the max value column. In the example dataset above, highlight E4:G4 and insert a chart.

Select a doughnut chart.

Under the Setup menu, make sure to check the “Switch rows/columns” checkbox, so your chart looks like this:

Donut chart in Google Sheets

Under the customize menu of the chart tool, set the following conditions:

Background color: None
Chart border color: None
Donut hole size: 67%
Set Slice 2 color to none
Remove the chart title
Set the legend to none

This is what the inner donut should look like:

Donut chart in Google Sheets

Step 2: Create the middle circle

Repeat the steps above for the inner circle, but use the next row of data, choose a different color, and set the donut hole size to 77% (you may have to experiment with these percentages to line everything up at the end).

Drag the second donut chart on top of the first and line up the radial bars to get:

Radial Bar Chart in Google Sheets

Step 3: Create the outer circle

Again, repeat the steps above from the inner circle to create a third donut chart, using the third row of data, a different color, and setting the donut hole size to 81% (again, this might need tweaking to line everything up).

Drag this third donut chart on top of the other two and you have a radial bar chart in Google Sheets!

Radial Bar Chart in Google Sheets

Note on editing charts:

Since the charts are placed on top of each other, you’ll only be able to access the top chart to edit. You’ll have to move it to the side to access the chart underneath, and then move that one if you want to access the inner chart.

Step 4: Add the data labels

It gets messy to add the data labels to each chart through the chart editor, so I opted to create formulas to add my data labels into the cells next to each bar of the radial bar chart.

To access cells underneath the charts, click on a cell outside of the chart area and then use the arrow keys on your keyboard to reach the desired cell.

Once there, add the following formula:

=E6&": "&TEXT(F6,"#,0")

This formula uses the TEXT function to combine text and numbers in Google Sheets.

This shows the series name and value alongside each bar:

Data labels for radial bar chart in Google Sheets

To finish, remove the gridlines from your Sheet to give the chart a clean look.

Radial Bar Chart in Google Sheets

Can I see an example worksheet for the radial bar chart?

Yes, here you go.

Real World Examples of Radial Bar Charts

My friend Jeff Sauer, who founded Data Driven U to teach people data-driven marketing, contacted me recently about creating a radial bar chart for one of his workshops.

He is graciously sharing his report here, so you can see a radial bar chart with six rings:

Data Driven U chart

This is a screenshot of his Google Sheet!

(If you’re looking for top draw digital marketing, then you should definitely check out Jeff’s site: DataDrivenU.com This is not an affiliate link, just a personal recommendation!)

You’ve probably also seen a radial bar chart in the wild with the Apple Watch Rings Chart!

Apple Watch Ring Chart

Google Sheets REGEX Formulas

This post explores the Google Sheets REGEX formulas with a series of examples to illustrate how they work.

Regular expressions, or REGEX for short, are tools for solving problems with text strings. They work by matching patterns.

You use REGEX to solve problems like finding names or telephone numbers in data, validating email addresses, extracting URLs, renaming filenames containing the word “Application” etc.

They have a reputation for being hard, but once you learn a few basic rules and understand how they work you can use them effectively.

There are three Google Sheets REGEX formulas: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE.

Each has a specific job:

• REGEXMATCH will confirm whether it finds the pattern in the text.

• REGEXEXTRACT will extract text that matches the pattern.

• REGEXREPLACE will replace text that matches the pattern.

Let’s understand them with a series of examples.

💡 Learn more
Learn more about REGEX formulas in my latest course: The Google Sheets REGEX Formula Cookbook

Continue reading Google Sheets REGEX Formulas

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.

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. Remedial 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:

Switch Function in Google Sheets

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:

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:

(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.