Sheets: Split number into groups of 4 and 3

Assuming that A2 contains the number of people, C2 contains the larger allowed group size, and C3 contains the smaller allowed group size, try this:

D2:

=(A2 - D3 * C3) / C2

D3:

=arrayformula( 
  min( 
    (A2 >= C3) * mod(A2, C2) * quotient(A2, C3), 
    iferror( 1 / ( 1 / ( 
      not(mod(A2, C3)) * (A2 / C3) 
    ) ) ), 
    iferror( 1 / ( 1 / ( 
      not(mod((A2 - sequence(A2 / C3) * C3) / C2, 1)) 
      * 
      sequence(A2 / C3) 
    ) ) ) 
  ) 
)

This should work for any number of people and any allowed group sizes, but do note that not all numbers can be expressed as groups of just two sizes. For example, 5 cannot be expressed by any combination of 4 and 3. The formula will give a fractional number of groups for the larger group size in these cases.

There are some anomalous edge cases as well, such as trying to divide 7 people into groups of 4 and 2, because an odd number of people cannot be divided into groups that each have an even number of people.

For posterity, here is a simpler version of the formula that should work fine as long as the smaller allowed group size is 6 or less:

D3:

=ifs( 
  not(mod((A2 - 0 * C3) / C2, 1)), 0, 
  not(mod((A2 - 1 * C3) / C2, 1)), 1, 
  not(mod((A2 - 2 * C3) / C2, 1)), 2, 
  not(mod((A2 - 3 * C3) / C2, 1)), 3, 
  not(mod((A2 - 4 * C3) / C2, 1)), 4, 
  not(mod((A2 - 5 * C3) / C2, 1)), 5, 
  true, "insufficient number of cases" 
)

When you attempt to divide 5 into groups of 4 and 3, this simpler formula will give a negative number of groups instead of a fractional number of groups for the larger allowed group size.

Suppose your total "Number of People" were in A2 (with the header "Number of People" in A1).

The following formula in, say, cell C1 would produce the entire mini report:

=ArrayFormula({"People Per Group", "Number in Group";{{4;3},TRANSPOSE(VLOOKUP(A2,VLOOKUP(SORT(UNIQUE(FLATTEN(SEQUENCE(ROUND(200/4)+1,1,0,4)+SEQUENCE(1,4,0,3)))),SORT(SPLIT(FLATTEN((SEQUENCE(ROUND(200/4)+1,1,0,4)+SEQUENCE(1,4,0,3))&"|"&SEQUENCE(ROUND(200/4)+1,1,0)&"|"&SEQUENCE(1,4,0)),"|"),1,1,2,0),{1,2,3},FALSE),{2,3},TRUE))}})

As the number of total people increases, there are more and more possible ways to form groups of 4 and 3. This formula maximizes groups of 4 and then assigns groups of 3.

I've written this to handle up to 200 people. But if you needed more, it's an easy adjustment: you'd just change each of the three instances of 200 in the formula to, say, 500 or however many your max group might be.

try:

cell D2:

=QUOTIENT(A2, C2)

cell D3:

=IF(A2-(C2*D2)>=3, QUOTIENT((A2-(C2*D2)), C3), 0)