Trouble with nested SEQUENCEs

Copper Contributor

I'm trying to create an automatic musical bar-numbering system. I'm wanting, for a set number of rows, to count 4 subdivisions and 4 beats per bar. So, for example:

1.1.1

1.1.2

1.1.3

1.1.4

1.2.1

1.2.2

1.2.3

1.2.4

1.3.1

1.3.2

1.3.3

1.3.4

1.4.1

1.4.2

1.4.3

1.4.4

2.1.1

etc.

I've gotten as far as

=(INT(SEQUENCE(128,1,1,1/20))&"."&(INT(SEQUENCE(20,1,1,1/4))&"."&(SEQUENCE(4,1,1,1))))

but this will only produce the first four rows: after that it doesn't seem to know to repeat the third sequence command. Is there a way I can do this?

Appreciate anyone's help (or questions - completely understand if this doesn't make any sense to anyone else!!).

2 Replies

@Andrew_Patterson Let's say the sequence starts in A1, then the formula below will do what you described.

=TEXTJOIN(".",,INT((ROW()-1)/16+1),INT(MOD(ROW()-1,16)/4+1),MOD(ROW()-1,4)+1)

See attached, column A. It's dynamically linked to the row number through the ROW() function. If you don't want that you could base it on a SEQUENCE as shown in Columns F:G

Thanks so much for this - I'll use it in the interim - I was hoping to use the SEQUENCE function so I could set up an array and refer to other cells for altering parameters (different number of bars, different number of beats per bar, etc.).