# Distribution of cell totals, until maximum reached and then autofill other cells

Copper Contributor

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

# Re: Distribution of cell totals, until maximum reached and then autofill other cells

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(