How to create a sequence of numbers with repeat and increment at intervals in google sheets

I need a single cell formula to create a sequence of numbers with a limit in Google Sheets as shown in the image.

3 rows repeat the value

then Increment by 5

Upvotes: 1

Answers (4)

Reputation: 1

try:

=INDEX(FLATTEN(SEQUENCE(4, 1, 5, 5)*SEQUENCE(1, 3, 1, 0)))

or:

=INDEX(FLATTEN(5*MAKEARRAY(4, 3, LAMBDA(x, O ,x))))

Upvotes: 0

Reputation: 3207

A different approach, but probably not a good one...:

=arrayformula(mround(sequence(12,1,2),3)*(5/3))

Upvotes: 0

Reputation: 2891

Use this formula, you can adjust the Sequence() and REPT(rg&",",3) parameters to your need.

In this example Sequence(number_of_unique_numbers,columns,start_at,increment_by)

And REPT(rg&",",Repeat_N_times)

=ArrayFormula(FLATTEN(SPLIT(BYROW(SEQUENCE(3,1,5,5), 
 LAMBDA(rg, IF(rg="",,REPT(rg&",",3)))),",")))

Option 02
Based on Themaster - answer we use lambda with the names.
u unique
s start
r repat n time

=LAMBDA(u,s,r, FLATTEN(MAKEARRAY(u,r,LAMBDA(u,r,u*s))))
       (4,5,3)

Upvotes: 3

Reputation: 50914

Use MAKEARRAY with FLATTEN. Multiply the row index number by 5:

=FLATTEN(MAKEARRAY(4,3,LAMBDA(i,j,i*5)))

Upvotes: 3