Search code examples
google-sheetsarray-formulas

Google Sheets Equation for Sequence: 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, 3.1, etc

I'm looking for a way to generate the following sequence: 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, 3.1, 3.2, 3.3, 4.1, 4.2, 4.3, etc. Where the max number after the decimal is specified by the value in another cell, so for example could be updated to: 1.1, 1.2, 2.1, 2.2, 3.1, 3.2

I'd like to use the ARRAYFORMULA function so that it auto-populates for a specified number of rows.

I cannot figure out this out, any ideas? Must be Google Sheet and not Excel Thanks!

Solution

  • You can try this formula where A1 is the max number before the dot, and B1 - after:

    =ARRAYFORMULA(
  ROUNDUP((SEQUENCE(A1*B1, 1, 1))/B1)&"."& 
  (MOD(SEQUENCE(A1*B1, 1, 0), B1) + 1)
)

    Another option where you can specify the total number of rows (C1) and max number after the dot (B1):

    =ARRAYFORMULA(
  ROUNDUP((SEQUENCE(C1, 1, 1))/B1)&"."& 
  (MOD(SEQUENCE(C1, 1, 0), B1) + 1)
)