How to increment a columns' cell reference by 1, then by 2, then by 1 etc.

[JulianS96]

Hi Forum.

Just wondering how to increment a cell's reference in a certain pattern.
From googling I have found out how to increment the cell references consistently using

Please Login or Register  to view this content.

What I want to achieve, however, is to increment the cell reference by 1 for one instance, and then by 2 for the second instance, and then again by 1, then 2 etc...

The closest formula I found was "Use INDEX():

Please Login or Register  to view this content.

Where /2 is the repetition wanted and the +2 is the starting row."

This formula, however, just repeats the same value and then increments by two and then repeats the same value then by 2 again, which is very close.
Any ideas?

[bebo021999]

Try with offset:

B12:

Please Login or Register  to view this content.

Drag down

[JulianS96]

Perfect works nicely! Thanks bebo!
If I wanted to modify the cell "jumps" what numbers would I need to modify?

For example if I wanted the Cell to increment by 1 for 4 cells, and then on the 5th cell increment by 4, then 6th cell increment by 1 for the next 4 cells.

[Bo_Ry]

Please try at B2

=INDEX(A2:A11,(ROWS(B$2:B2)+1)/2)

[JulianS96]

Please try at B2

=INDEX(A2:A11,(ROWS(B$2:B2)+1)/2)

Thanks Bo_Ry. That formula works nicely as well.
In reference to my above question, What numbers would I modify if I wanted to achieve a pattern of "Increment cell reference by 1 for 4 iterations, then by 4 for 1 iteration, then by 1 for 4 iterations etc."

[bebo021999]

I am not sure 100% understanding, but following is scenarios of:
Value is from A4 down
Other column, i.e, from L4 down to take value in column A, with this frequency:
1
1
1
1
4
1
1
1
1
then repeat for next set of 9 rows.

[JulianS96]

Hi bebo, I have made a new sheet to display what I mean. In the top you can see I have worked out how to keep the increment by 1 for however many cells by changing the divisor. The incremental of + 5 after that is what I am unsure of.
I meant to Say increment of +5 before as it is a gap of 4 rows that I am hoping for.

[bebo021999]

So, for each row, current row will move down:
0
0
0
0
4
4
4
4
8
8
8
8
...

In J28:

Please Login or Register  to view this content.

[JulianS96]

Yep that'll do it haha!
What is the modification number for the modification of +5 in that formula?
Nvm i worked it out!
The divisor e.g. "/4" in this case is the number of repeats of "+1 cell ref" and the "*4" is the number of cell references to increase by after the number after the divisor has completed. Then repeat.
Thanks bebo!

[bebo021999]

row(1:1)-1 drag down to be 0,1,2,3 (integer no repeat)
(row(1:1)-1)/4 to be 0/4,1/4,2/4,3/4, then, 4/4,5/4,6/4,7/4,then... with each group have the same rounded interger
int(.../4) drag down to be 0,0,0,0,1,1,1,1,2,2,2,2 (each repeat 4 times)
Then* 4 to become 0,0,0,0,4,4,4,4,8,8,8,8,...



INT((ROW(1:1)-1)/4)*4

[JulianS96]

Very detailed explanation thank you for that!