Reputation: 417
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
Views: 1263
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)
Used formulas help
ARRAYFORMULA
- FLATTEN
- SPLIT
- BYROW
- SEQUENCE
- LAMBDA
- IF
- REPT
- MAKEARRAY
Upvotes: 3
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