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

Question

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.

kishkin · Accepted Answer · 2022-02-16 06:56:25Z

2

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)
        )
    )
  ),
  ",$",
)

edited Feb 16, 2022 at 6:56

answered Feb 16, 2022 at 6:50

kishkin

5,3551 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Riyaz Mansoor Over a year ago

Thanks mate. I'm seeing QUERY() is the life saver in these types of situations. Great job.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related