Forum Discussion

Turney_Ryan's avatar
Turney_Ryan
Copper Contributor
Sep 05, 2023

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

Basket 6 - 130 Oranges

 

But I want to know how many I can fit in a crate, which can hold 350 Oranges, without overfilling.

And, if possible to work out the best calculation - so basket 4 wouldn't fit, but basket 5 would. 

So for example:

 

Basket 1 - 100 Oranges = Crate 1(100 total)

Basket 2 - 50 Oranges = Crate 1 (150 total)

Basket 3 - 87 Oranges = Crate 1 (237 total)

Basket 4 - 210 Oranges = Crate 2 (210 total)

Basket 5 - 65 Oranges = Crate 1 (302 total)

Basket 6 - 130 Oranges = Crate 2 (340 total)

Formulas and Functions

1 Reply

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 05, 2023

    Turney_Ryan 

    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)
)

     

Resources