Tips & Tricks 131 – Repeat a Number and Increment and Repeat….

Suppose, you have been given the task of repeating a number and increment that number and repeat it. For example –

1,1,1,1,2,2,2,2,3,3,3,3…..(Here, we are repeating it 4 times and incrementing and repeating 4 times again and so on)

Then you can use following formula

=ROUNDUP(ROWS($1:1)/4,0)

Suppose, you want to start the number with 5 not 1, then you can use below formula –

=ROUNDUP(ROWS($1:1)/4,0)+4

Hence, general structure of the formula is

=ROUNDUP(ROWS($1:1)/X,0)+Y-1

X – Number of times a particular number is repeated
Y – Starting Numbers

Hence, if you want to start with number 7 and you want to repeat it 5 times, then following formula should be used

=ROUNDUP(ROWS($1:1)/5,0)+6