Formula Challenge #7: Generate A Repeating Sequence Of Numbers

This Formula Challenge originally appeared as Tip #243 of my weekly Google Sheets Tips newsletter, on 27 February 2023.

Congratulations to everyone who took part and submitted a solution!

Sign up for my newsletter to receive future Formula Challenges:

 

Formula Challenge 7 Question:

Can you create a single formula that creates the output shown in this image:

Irregular Sequence

Assume a height of 100 rows.

Solutions

There were a lot of great solutions! Here are 8 of the best:

Solution 1: Text Approach

=FLATTEN(SPLIT(REPT("1,299,13,50,",25),","))

This formula starts with a text representation of the number sequence “1,299,13,50,”.

It uses REPT to repeat the sequence, SPLIT to separate by the commas, and finally FLATTEN (or TRANSPOSE) to convert to a column format.

This is the shortest formula by character count.

Solution 2: IF and SEQUENCE

=ArrayFormula(FLATTEN(IF(SEQUENCE(25),{1,299,13,50})))

In this formula, SEQUENCE generates a column of 25 numbers. An IF formula wrapper then takes each number as an input and, since numbers are truthy values, the IF outputs the array.

The FLATTEN function then stacks into a single column.

Solution 3: MAKEARRAY + CHOOSE

=MAKEARRAY(100,1,LAMBDA(i,_,CHOOSE(MOD(i-1,4)+1,1,299,13,50)))

The MAKEARRAY function generates an array of 100 rows in 1 column. The LAMBDA function uses MOD to create a sequence 1,2,3,4,1,2,3,4,1,2,3,4…

The CHOOSE function converts the 1,2,3,4 into the sequence 1,299,13, or 50.

Also, note the use of the underscore “_” to denote the argument in the LAMBDA that is not used in the formula.

Solution 4: CHOOSE + SEQUENCE

=ArrayFormula(CHOOSE(MOD(SEQUENCE(100)-1,4)+1,1,299,13,50))

The SEQUENCE function generates a list from 1 to 100. MOD converts this to a repeating sequence 1,2,3,4,1,2,3,4,1,2,3,4,…

The CHOOSE function then sets the final values and the whole is wrapped with ArrayFormula to output the full array.

Solution 5: SEQUENCE + Array

=ArrayFormula(FLATTEN(SEQUENCE(25,1,1,0)*{1,299,13,50}))

The SEQUENCE function generates 25 rows of 1’s, which is multiplied by the array literal {1,299,13,50}. This creates 4 columns, one for each number in the sequence. FLATTEN then stacks them in a single column.

Solution 6: Matrix Multiplication

=ArrayFormula(FLATTEN( MMULT(SEQUENCE(25,1,1,0),{1,299,13,50})))

This is essentially the same as the previous solution (#5) but uses the MMULT function to multiply the two arrays.

Solution 7: Array + LEFT trick

=ArrayFormula(1*FLATTEN({1,299,13,50}&LEFT(F1:F25,0)))

This unusual solution uses the LEFT function to create an empty array of 25 rows, because the length is set to 0.. It can use any column except the one containing the formula itself.

Solution 8: SWITCH + ROW

=ArrayFormula(
SWITCH(MOD(ROW(A1:A100),4),
1,ROW(),
2,ROW()+298,
3,ROW()+12,
ROW()+49))

This novel approach uses MOD with the ROW function to generate a repeating sequence. This is fed into a SWITCH function to convert to the desired output.

Notes

The newly released TOCOL function could also be used in this challenge, but was unavailable at the time of writing.

The best way to explore and understand these formulas is to use the Onion Framework to create them for yourself, starting with the innermost function.

Find previous Formula Challenges archived here.

11 New Analytical Functions In Google Sheets For 2023

Coming hot on the heels of last year’s batch of new lambda functions, Google recently announced another group of new analytical functions for Sheets.

Included in this new batch are the long-awaited LET function, 8 new array manipulation functions, a new statistical function, and a new datetime function.

Let’s begin with a look at the new array functions. The LET function is at the end of the post.

  1. TOROW Function
  2. TOCOL Function
  3. CHOOSEROWS Function
  4. CHOOSECOLS Function
  5. WRAPROWS Function
  6. WRAPCOLS Function
  7. VSTACK Function
  8. HSTACK Function
  9. MARGINOFERROR Function
  10. EPOCHTODATE Function
  11. LET Function

Continue reading 11 New Analytical Functions In Google Sheets For 2023

How To Create A Google Sheets Drop-Down Menu

In this post, we’ll look at how to create a Google Sheets Drop-Down Menu. Here’s an example of a drop-down menu to record the status of deals in a real estate deal pipeline:

Google Sheets Drop-Down Chips

Drop-down menus are great for data entry and making your Sheets dynamic.

In this post, we’ll explore both of these techniques with examples.

But first, let’s see how to create a Google Sheets drop-down menu.

Continue reading How To Create A Google Sheets Drop-Down Menu

XMATCH Function in Google Sheets

The XMATCH function in Google Sheets is a new lookup function in Google Sheets that finds the relative position of a search term within an array or range. It’s an evolution of the original MATCH function.

Here’s a simple XMATCH function that finds the position of the search term “Cho Oyu” in the list of the highest mountains in the world:

=XMATCH(E2,A2:A15)

In the Sheet:

Xmatch Function Google Sheets Simple Example

And here’s how it works:

It looks for the search term from cell E2 (“Cho Oyu”) in the range A2:A15, then returns the position of the search text within this range. Note that the result is relative to the range, irrespective of the row number.

Xmatch Function Google Sheets Explainer

Notice how, unlike a regular MATCH function, you don’t have to specify the “0” search type for an exact match. It chooses the exact match, which is by far the most common use case, by default (in contrast to the MATCH function where you have to add the 0 to explicitly confirm exact matching). More on the search types below.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading XMATCH Function in Google Sheets

Google Sheets Advent Calendar

This year, I created a Google Sheets Advent Calendar, which you can see in action here:

Google Sheets Advent Calendar

It was a fun project with some interesting techniques, which are explored below.

You could easily modify it for your own example, or use these techniques in different scenarios.

Plus, if you’re too cheap to buy a physical advent calendar, this lets you enjoy the fun of opening a door each day to reveal something, but for free!
Continue reading Google Sheets Advent Calendar