anwwwar
anwwwar

Reputation: 417

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

enter image description here

Upvotes: 1

Views: 1263

Answers (4)

player0
player0

Reputation: 1

try:

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

2

or:

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

1

Upvotes: 0

The God of Biscuits
The God of Biscuits

Reputation: 3207

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

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

Upvotes: 0

Osm
Osm

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)))),",")))

enter image description here

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)

enter image description here

Used formulas help
ARRAYFORMULA - FLATTEN - SPLIT - BYROW - SEQUENCE - LAMBDA - IF - REPT - MAKEARRAY

Upvotes: 3

TheMaster
TheMaster

Reputation: 50914

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

=FLATTEN(MAKEARRAY(4,3,LAMBDA(i,j,i*5)))
Output
5
5
5
10
10
10
15
15
15
20
20
20

Upvotes: 3

Related Questions