Distribute items by weight into buckets
I am helping a friend to create a sheet to calculate items counts that can be placed in buckets such that the weight of the buckets is balanced (as much as possible)
Item Data Available
| Item | Count | Weight |
|---|---|---|
| A | 20 | 5 |
| B | 10 | 25 |
| C | 12 | 15 |
| D | 5 | 40 |
Number of Buckets: 2 (for this example)
Max Bucket Count: 30 (for this example)
The Total Weight of items is 730
Based on the bucket count, we can place items into the two bucket as
| Item | Bucket 1 | Bucket 2 |
|---|---|---|
| A | 6 | 14 |
| B | 5 | 5 |
| C | 6 | 6 |
| D | 3 | 2 |
In this case each bucket weighs 365. Bucket 1 count is 20 and Bucket 2 count is 27
As the limit of the bucket is 30, we are good.
But if the bucket limit is 24, Bucket 2 should not be possible.The distribution may have to be something like
| Item | Bucket 1 | Bucket 2 |
|---|---|---|
| A | 11 | 9 |
| B | 5 | 5 |
| C | 4 | 8 |
| D | 3 | 2 |
Bucket 1 weight is 360 and Bucket 2 is 370 and the item counts in both are in the limit (24).
I think I can do this using a macro, but would like to know if it can be done using formula?
Posted Feb 22, 2016, 5:00 AM UTC