excelsolverexcel-solver

Finding best result with Excel Solver using integers for input (linear equation)


I have a stock of four products with a defined amount avaible and a defined unitary price. From the client I receive a flat amount of money for the products.

Given this amount I have to find the best combination of products to get reach the closest amount to what the client defined. For example: Client wants 100.000, need get the closest to 100.000.

The table is the following:

Product Price Amount Avaible
1 15000 1000
2 20000 1000
3 5500 1000
4 5000 1000

enter image description here

Putting in a linear equation would be like this:

CLIENT'S INVESTMENT = Price1 * AMT1 + Price2 * AMT2 + Price3 * AMT3 + Price4 * AMT4

or

V = 15000x + 20000y + 5500z + 5000t

Given that, I want to use solver to reach the best distribution of products in which the difference between what he asks and what I can give is minimum.

With solver I can get this easily and quickly but I get non-integer numbers that doesn't work for the stock. I tried rounding all variables down but isn't always the optimal solution, rounding up one of them can get a better result (really rare).

Restricting in Solver to the variables to be integers takes an infinite amount of time running and using Solver in the rounded up values always gives me an error.

Is there a better solution, or a routine to try the best round (up/down) combination in each case (maybe 16 iterations?).


Solution

  • I can’t see why you need cells F9:F12...

    I would set the decision variables to cells C2:C5 and set a constraint for them to be integer.

    If the total of amount sold is to be 100000 then contrain the sum of those cells to be less than or equal to 100000 and set the objective to be the sum of the totals to a maximum.