Using SEQUENCE Array Formulas - Varying Repeating Sequence

Hey all,

I'm trying to write an array formula using SEQUENCE. I'm new to using array formulas so I'm not sure if this can be done but maybe one of you smart folks knows of a way.

Essentially, I have a repeating sequence. Every third repetition of the sequence should be slightly different:

1 2 3 4 1 2 3 4 1 2 3 4 5 1 2 3 4 1 2 3 4 1 2 3 4 5 ...

This entire series (1 2 3 4 1 2 3 4 1 2 3 4 5) should repeat 4 times, giving results across 52 cells.

I am able to write a formula to repeat up to 4 quite easily:

=MOD(SEQUENCE(,13*4)-1,4)+1

I can also write a formula that alternates between 4 4 5, which I believe should have some role in the final formula (essentially replacing the hard-coded 4 in the formula above to dynamically determine whether the sequence should repeat from 1 to 4 or 1 to 5).

=IF(MOD(MOD(SEQUENCE(,12)-1,12)+1,3)=0,5,4)

Replacing the 4 in the first formula with the second formula obviously does not return the desired results. I know I need to consider the remainder after division somehow but am having trouble wrapping my mind around this one.

I would greatly appreciate any help.

I did some digging and was able to modify an answer I found elsewhere (I would post the link but I am too new to do so; it is Eric W.'s response to "Alternating sequence of numbers" on Mrexcel.com) It's a little crude in that I need to manually list my sequence but it works! Any other possible solutions would be greatly appreciated as well.

=INDEX({1,2,3,4,1,2,3,4,1,2,3,4,5},MOD(SEQUENCE(,13*4,0),13)+1)

How about

Formula:

Please Login or Register  to view this content.

This works as well and is very elegant! I'll have to do some research on LET and FILTER to understand the logic better. Thank you very much for your response!

Glad to help & thanks for the feedback