Hi Reader,

Welcome to the Google Sheets Tips newsletter #243!

➜ News

I.
​It's time to retire the beloved Extreme Formulas course, which was launched in 2019.

I'm retiring it because it doesn't include the new function paradigms like LAMBDA and LET.

However, the majority of examples, like a single array formula to create a year calendar, are still valid.

To celebrate its retirement, I'm offering a farewell discount this week only:

​Grab Extreme Formulas for $24.99 only (usually $99)

After this week, you won't be able to enroll.

Note: All students of the course, whether you're new this week or an existing student, will continue to have access forever.

II.
​Some exciting new smart canvas features coming to Google Sheets and Docs!

I like the sound of the @map place chip, which shows a preview of locations or directions, directly in your Sheet.

These updates are rolling out in February and March.

​Read more about the new Smart Canvas features​

_______

➜ Google Sheets Tip #243: Repeating Number Sequence Formulas

Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

The SEQUENCE function is one of the most useful functions in Google Sheets. It generates sequences of numbers, which are super helpful for numbering and grouping data.

The function looks like this:

The columns, start, and step arguments are optional.

In its simplest form, the formula:

generates 10 numbers in a column, like so:

1

2

3

...

10

It can be used to generate columns, rows, 2-d arrays, and many different types of lists.

In today's email, we'll look at two slightly more advanced examples:

Repeating Number Sequence Formulas

Before looking at the formulas below, consider these repeating sequences and see if you can figure out how to generate them:

The first sequence repeats each number 3 times, before incrementing to the next number, i.e. 1, 1, 1, 2, 2, 2, ...

The second sequence repeats the numbers 1, 2, 3 over and over, i.e. 1, 2, 3, 1, 2, 3, ...

I've set it to repeat the sequence three times, which gives 9 rows in total.

These types of sequences are useful for grouping rows of data, for example, putting names into different activity groups.

So, how do we create them?

1. Sequence: 1, 1, 1, 2, 2, 2, ...

Here are two formulas that generate the sequence 1, 1, 1, 2, 2, 2, ...

a) an ARRAYFORMULA with the SEQUENCE and ROUNDUP functions:

b) and here's an alternative version using the newer MAKEARRAY and LAMBDA array functions:

2. Sequence: 1, 2, 3, 1, 2, 3, ...

This variation is a little more tricky and involved using the MOD function to generate the sequence 1, 2, 3, 1, 2, 3, ...

c) an ARRAYFORMULA with SEQUENCE

d) the MAKEARRAY equivalent:

Making Changes

The value 9 determines how many rows are created and the value 3 changes the period.

For example, changing the 9 to 100, and the 3 to a 5 will generate 100 rows with repeating sequences up to 5.

---

Now it's your turn -->

Formula Challenge 7: Irregular repeating sequences

Suppose you want to create this repeating sequence, for 100 rows:

How would you do it with a single formula in A1?

Hit reply to share your ideas and I'll share the best solutions in next week's newsletter.

​See the first 6 formula challenges here​

_______

If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. As one course departs this world, another is born and the circle of life continues...

I'm recording videos for a new Apps Script course designed for beginners or intermediate users.

I believe it will be the BEST way to master the foundations of Apps Script.

If all goes to plan, I hope to open it for enrollment in late March!