To match 2 qty with kg's

[Bruce Johnson]

Hi all I am new to this forum.
Could someone point me in the right direction.

I have 4 different size cylinders namely 48kg , 19kg , 14kg , 9kg
I have 48000 kg bulk
and 6000 seals to put on the cylinders after filling.

So I now need to justify the use of 6000 seals on random size cylinders to use up the 48000kg bulk. (+- a couple of kg or seals)
By inserting the bulk kg's and the number of seals I have I would like to arrive at a random num of 48,19,14,9kg that will cover the amount of bulk and also to use up the 6000 seals (+-)

I hope this is a good enough explanation.

Thanks
Bruce

[MrShorty]

Feels like a knapsack type problem. I would probably use Solver for this one:

1) Have a column with the size/massof each cylinder.
2) Have a column with the number of each cylinder to be used.
3) A SUMPRODUCT() function using both columns will calculate the total mass for the current scenario.
4) A SUM() function on the number of cylinders column will compute the number of cylinders (and, therefore, seals) to be used.
5) Call Solver and tell it to:
5a) Set Target cell: the SUMPRODUCT() cell
5b) To a value of 48000
5c) By changing: Then number of each cylinder column.
5d) Subject to the constraint that the number of each cylinder cells are all integers
5e) Subject to the constraint that the SUM() cell is 6000.

Then let Solver do its thing. Assuming Solver finds a solution, evaluate the solution Solver found.

[Bruce Johnson]

Good day MrShorty,
Im sorry I lost you at point 2 as I dont know how many cylinders are to be used.

That is what I am looking for, all I have to work with is 48000kg and 6000 seals/cylinders of different content qty.
So I need a qty of each size/seals randomly that will deplete the 48000kg to the nearest 100 kg or seals.
I think another way is ,how many seals of each size do I need to use up 48000kg to the nearest couple of kgs
sorry to be a pain.

[MrShorty]

I dont know how many cylinders are to be used.

Don't worry about that at this point in programming. Solver will change the values you enter in this column as it attempts to find a solution. You just need something in these cells (mostly for your benefit while programming the spreadsheet and getting it ready for Solver) so that you can build the SUMPRODUCT() and SUM() functions. You can enter any random numbers you want (or even make them all 0 if you prefer). By the end of step 5, Solver will (hopefully) be able to come up with a solution.

[astupig]

The total weight should be 480,000kg instead of 48,000kg, correct?
if 48,000 kg, the minimum weight to use the 6000 seals is to allocate it in the 9kg cylinder, which is 54,000kg.

[Bruce Johnson]

Hi astupig,
Thanks for the input, yes it is 48000 kg .
that is changing every delivery but the seals change every month and the cylinder size is a constant and never changes.

Mrshorty thanks for your help , every thing fell into place once I got my head around (sumproduct) all worked fine.
The result came out fast and to the 6th decimal place so with a bit of tweaking I am as happy as a pig in.....
Thanks to all.