ArrayFormula - Number Partitioning row wise - Attempt to use a single column formula for all rows

Question

Ref link

Problem description. From a CSV string ("10,13,25,17") each number is partitioned X times such that sum is equal to the original number. Only interested in the average number(s).

Eg: 10 partition 3 = { 3,3,4 }, 15 partition 2 = { 7,8 }, 10 partition 2 = { 5, 5 }

So the input to output (JOIN-ed) looks like ==>

Part("22,25,30",12) = "1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,2,2,2,2,2,2,3,3,3,3,3,3"

I have successfully created the ArrayFormula for above. But am stuck on how to apply it to a whole column (vertically). Any ideas? (recently someone in StackOverflow introduced me to QUERY header smush - but still stuck on this).

On a separate note - I wonder if "Named Formulas" or "Formula Aliases" are ever coming to Google Sheets. Reusing formula components will be a life saver with these huge ArrayFormulas. And yes - I have sent this query to Google many times over a few years.

Answer 1

Horizontal query smash (or is it vertical?...) to the rescue (formula is in D10):

=ARRAYFORMULA(
  IF(
    A10:A = "",,
      REGEXREPLACE(
        TRANSPOSE(QUERY(TRANSPOSE(
          IF(
            SPLIT(A10:A, ",") = "",,
                REPT(
                  INT(SPLIT(A10:A, ",") / Months) & ",",
                  Months - MOD(SPLIT(A10:A, ","), Months)
                )
              & REPT(
                  INT(SPLIT(A10:A, ",") / Months) + 1 & ",",
                  MOD(SPLIT(A10:A, ","), Months)
                )
          )
        ),, 10^7)),
        "\s+|,\s*$",
      )
  )
)

---

Notice that you original solution was simplified and used in here. Simplified original solution is in B3:

=REGEXREPLACE(
  CONCATENATE(
    ArrayFormula(
        REPT(
          INT(SPLIT(A2, ",") / Months) & ",",
          Months - MOD(SPLIT(A2, ","), Months)
        )
      & REPT(
          INT(SPLIT(A2, ",") / Months) + 1 & ",",
          MOD(SPLIT(A2, ","), Months)
        )
    )
  ),
  ",$",
)