How to Group a list of values into buckets with a Maximum Total

I am trying to get excel to automatically group a list of values into buckets with a maximum total.

Basically -- this is for a calculator for a lighting product, and each fixture has a different wattage, and I want excel to see the wattage of each fixture, and efficiently group the values onto drivers (transformers).

For instance, say these are the wattages currently in the list (the wattages and quantity of values will change based on the job being quoted):
24
55
18.5
44.4
20
31.5
47
17

And say for this, the maximum wattage per driver is 80 watts. If I were doing this manually, I would figure out how I could most efficiently group these values to stay within the 80-watt max. Is there a way to have excel do this calculation? It may not be as perfect or efficient as me doing it manually, but I am just trying to find the best excel alternative to use in this calculator.

I do not want to use VBA code for this. So please keep that in mind.

Let me know if you have any ideas, or if this is unclear and you have questions. I appreciate the help so much.

- Colby

Hi Colby:

Have you tried using Excel's built-in SOLVER for this? Has an intuitive interface, where you apply a number of constraints; such as ballast wattage. . ?, and it'll offer the best matches. Might get you up and running.

Pete

Originally Posted by PeteABC123

Hi Colby:

Have you tried using Excel's built-in SOLVER for this? Has an intuitive interface, where you apply a number of constraints; such as ballast wattage. . ?, and it'll offer the best matches. Might get you up and running.

Pete

Hi Pete,

I have not. I am not familiar with it. Is it something that can be employed within a much larger calculator? (This is going to be a small calculation in a much larger system of calculations to find a Bill of Materials for entire jobs, based on individual run lengths).

Best Regards,
- Colby

Hi Colby:

BOM's can get messy, and it sounds like you're in the middle of extracting BOM's, and identifying how many widgets can fit into a 80- watt driver as a component of the BOM. An uploaded sanitized example of your data would help the forum members point you in the right direction; but could get really convoluted if it isn't complete and laid out properly. Suggest scraping YouTube for an introductory solver video, see if it's the right tool to play with (guessing it's not).

Pete

A possible setup using solver could look like this. The number of buckets will be determined by the total load and the max load per socket.

Using 80 as max per socket gives the distribution of 47, 79.9, 51.5, 79. Setting 70 as max load per socket gives a distribution of 67, 67, 55, 68,4 a more even distribution of the loads per socket. To test for
results using 60 as max load to test if socket distribution can be more even the number of buckets must be increased to 5 (5*60=300) as capacity of total buckets must exceed 257.4

To test and run this model Solver must be installed as it's not installed by default when installing Excel 2010.

Alf