CODEWITHSUNDEEP
excelexcel-formula
Pooman19
Pooman19

Reputation: 13

Create Sequence in Excel with repeating numbers every nth time

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.

Upvotes: 1

Views: 909

Answers (2)

EconJohn
EconJohn

Reputation: 175

Another way to do this is with the following code:

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

I got this idea from the following thread: Is there a way to find out the values ​of 4 cells are the same or different in MS Excel

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

For values in a column, in A1 enter:

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

and copy downward:

enter image description here

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)

Upvotes: 2

Related Questions