Suppose, you have been given the task to generate a sequence of numbers and repeat them. For example –
1,2,3,4,1,2,3,4,1,2,3,4
You can use the below formula and drag down –
=MOD(ROWS($1:1)-1,4)+1
Replace 4 with with any other number to generate any other sequence. Hence, if you want to generate 1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10 then formula becomes –
=MOD(ROWS($1:1)-1,10)+1
The structure of the formula is
=MOD(ROWS($1:1)-1,X)+Y
X – Number of numbers
Y – Starting Number
Utilizing above formula, you want to generate the sequence 5,6,7,8,9,10,5,6,7,8,9,10,5,6,7,8,9,10, then use below formula (You need 6 numbers and stating number is 5)
=MOD(ROWS($1:1)-1,6)+5