profile

Google Sheets Tips

Sheets Tip 243: Repeating Sequences And Formula Challenge 7

Published about 1 year ago • 2 min read


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:

=SEQUENCE(rows, columns, start, step)

The columns, start, and step arguments are optional.

In its simplest form, the formula:

=SEQUENCE(10)

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:

=ArrayFormula(ROUNDUP(SEQUENCE(9)/3))

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

=MAKEARRAY(9,1,LAMBDA(i,j,ROUNDUP(i/3)))

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

=ArrayFormula(MOD(SEQUENCE(9,1,0),3)+1)

d) the MAKEARRAY equivalent:

=MAKEARRAY(9,1,LAMBDA(i,j,MOD(i-1,3)+1))

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!

Google Sheets Tips

by Ben Collins

Get better at working with Google Sheets! Join 50,000 readers to get an actionable tip in your inbox every Monday.

Read more from Google Sheets Tips

Hi Reader, Welcome to the Google Sheets Tips newsletter #296, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: TAPLIO Grow your LinkedIn audience with the power of AI Taplio helps you grow and leverage your personal brand on LinkedIn to build an audience and attract more leads and opportunities. Create high-performing LinkedIn posts, engage with your target audience, find leads and measure all your results....

4 days ago • 2 min read

Hi Reader, Welcome to the Google Sheets Tips newsletter #295, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SHEET CHAT Your Powerful AI Google Sheets Copilot Sheet Chat is an AI-powered Copilot that helps you work efficiently in Google Sheets and Google Docs. Use it for free with your OpenAI API key and hosting, and create your own custom agent based on your specific requirements. Try it for free!...

11 days ago • 3 min read

Hi Reader, Welcome to the Google Sheets Tips newsletter #294, your Monday morning espresso, in spreadsheet form! A big thank you to this week's sponsors who help keep this newsletter free to read: SAPIENGRAPH Add LinkedIn and Crunchbase data via custom formulas to GSheets Sapiengraph makes it easy to fill your Google Sheets with fresh, accurate data from LinkedIn and Crunchbase. Using our intuitive custom formulas: VCs can track the employee count of portfolio companies with...

18 days ago • 2 min read
Share this post