How to have excel find the best fit of a number into 2 choices

[MosheV]

I would like to know how to divide a number (pounds of milk) into 2 possible (truck) sizes, always using the biggest trucks possible, and minimizing any empty space.
e.g. I’ve decided that I need to order 30lbs of milk and I have trucks that carry 5lbs and 6lbs, I would need to use 5, 6lb trucks. But if I need 32lbs of milk I would use 2 6lb trucks and 4 5lb trucks (numbers shrunk for simplicity’s sake).

 

[estevaoba]

I guess the best way to accomplish that is the Excel add-in solver.
To load the Solver Add-in, go to page below:
https://support.office.com/en-us/ar...in-Excel-612926fc-d53b-46b4-872c-e24772f078ca

Set up your data like table below.
In “Formula” ($A$3), paste this:
=SUM(B3*B2,C3*C2)
Leave cells X and Y blank.

Tap the Data menu, and find Solver far right.
In solver first line, Set Objective, click your target cell $A$3 (where you entered formula).
In “To”, select “Value of” and type desired target value, for example, 32.
In “By changing variable cells”, select range where X and Y are ($B$3:$C$3).
In “Subject to constraints”, tap Add, then paste the same range ($B$3:$C$3) in “Cell reference”. In center box, select “Int” for integer numbers. Tap OK.
In “Select solving method”, select Simplex LP.
Tap Solve to finish.

	A	B	C
1	Qty	Truck 1	Truck 2
2	Blank	5	6
3	Formula	X	Y

<tbody>
</tbody>

 

[MosheV]

Thank You, this is great, but...

--- In “To”, select “Value of” and type desired target value, for example, 32. ---

Is it possible to have the "To" value be a number taken from excel (since I add up how much milk I will need to come to the number 30 or 32)

 

[estevaoba]

Try adding a constraint:

In “To”, select “Max” or "Min" and leave box at right blank.

In “Subject to constraints”, tap Add. In “Cell reference” click cell $A$4, In center box, select “=”, in right box, click cell $A$3. Tap OK

Now you can have cell A4 for the milk quantity you need.