excelexcel-formula

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.

Solution

  • 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)