Create Sequence in Excel with repeating numbers every nth time

Question

I am trying to create a fairly basic sequence using a formula whereby columns follow the following order:

2,2,2,2,6,6,6,6,10,10,10,10,14,14,14,14 .....

I have tried a series of offsets and if statements but couldnt get it to function.

Gary's Student · Accepted Answer · 2021-01-21 15:53:08Z

2

For values in a column, in A1 enter:

=4*(ROUNDUP(ROW()/4,0))-2

and copy downward:

If you have Excel 365 and want to also make a comma-separated list, then pick another cell and enter:

=TEXTJOIN(",",TRUE,A:A)

If you have Excel 365 and want to make a comma-separated list without using a "helper column", then pick a cell and enter:

=TEXTJOIN(",",TRUE,4*(ROUNDUP(SEQUENCE(20)/4,0))-2)

answered Jan 19, 2021 at 12:39

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

thank you, thats awesome, it worked really well

@Pooman19, if it has helped and has answered your question consider to accept it as such by clicking the checkmark to the left of the answer.

EconJohn · Accepted Answer · 2024-12-16 23:50:51Z

0

Another way to do this is with the following code:

=IF(AND(A1=A2,A2=A3,A3=A4),A4+4,A4)

answered Dec 16, 2024 at 23:50

EconJohn

1751 silver badge14 bronze badges