Consecutive numbers in column - 1 2 3, 1 2 3

[prochasska]

Hello,

I have a spreadsheet where I want to enter consecutive numbers every three rows (the number of rows can vary, it can be two, three, four, five, etc.).
See example below.

London 1
Brighton 2
LA 3
Italy 1
Spain 2
Austria 3

Please can you advise on a formula?

BR,
Monika

 

[jmacleary]

Hi Monika

Put a 1 in the start cell (I've assumed H3 for no particular reason), then put this in H4 and copy down: =MOD(H3,3)+1

You could replace the 3 with any different value, or refer to a cell with the number in it.

 

[steve the fish]

YOu can use

=MOD(ROWS($A$1:A1)-1,3)+1

and change the 3 in the formula or point it to a cell reference if you want.