Forum Discussion
Distribution of cell totals, until maximum reached and then autofill other cells
As an example: I have 2 columns with allocation and quantities. Example: Basket 1 - 100 Oranges Basket 2 - 50 Oranges Basket 3 - 87 Oranges Basket 4 - 210 Oranges Basket 5 - 65 Oranges B...
This type of problem has been posted on this site several times in the past. You can solve it with Solver or with a formula. I've chosen to solve it with a formula. My solution presents the possibilities that are valid. I chose not to select the 2 or 3 combinations from the list that are "best" because the method behind which to select may vary depending on the needs. I leave that decision to you.
=LET(
Total_baskets, COUNTA(Baskets),
bin_matrix, MOD(
INT(SEQUENCE(2 ^ Total_baskets, , 0) / 2 ^ SEQUENCE(, Total_baskets, 0)),
2
),
Scenarios, bin_matrix * TOROW(Oranges),
Crate_totals, MMULT(bin_matrix, Oranges),
Results, SORT(HSTACK(Scenarios, Crate_totals), Total_baskets + 1, -1),
FILTER(Results, TAKE(Results, , -1) <= 350)
)