# How to limit Solver's choices

1. ## How to limit Solver's choices

Does anyone know if there's a way to limit the number of variables solver gives you? I have a slitting optimizer where the user is to select a "Raw" material# and the pivot table will then show all the different "Semi" materials/slits that can be cut out of that Raw material (there can be up to 10 or less different Semi materials that come out of one Raw material #). The user can also specify below what usage % they want solver to ignore, which I've built in with formulas. I have helper columns to force it to calculate at least one slit for every Semi material, except for the first help column which I want to look at only the largest usage item.

My question is can I force Solver to only give me 3 or less different slit widths in each scenario (shown in the #Slits columns). If you look at column M (under #Slits2) you see it gave me 4 different slit widths but I need to limit it to 3 or less.  Register To Reply

2. ## Re: How to limit Solver's choices

Here is solution for your problem, but it requires OpenSolver.

The easy path would involve adding a formula such as

=COUNTIF(Slits1,">0")

and then constraint this sum to the required number of parts. Unfortunately, Solver will have a hard time finding a solution, because there are many integer variables in your model, and the COUNTIF functions are non-linear. You may have some luck with the evolutionary engine, but I wouldn't bet on it.

Instead, we can use a set of binary variables and two simple constraints, so that when a part is selected by OpenSolver (that is, has value > 0), the corresponding binary variable switches to 1.
With this helper variables in place, we take the sum of each column and set it smaller than a desired number.

Please note I have reordered your columns. As you can see, the number of constraints is now reduced.
There is a new constant, Max_cuts, that is the ratio between the Max Width and the smaller slit width. It is used to help OpenSolver putting a cap on the max value an integer var can take.

This model is hard even for OpenSolver, with a Branch and Bound tolerance of 2% it needs more or less half a minute to find a solution.

HTH,

Francesco  Register To Reply

3. ## Re: How to limit Solver's choices

Thank you Hydraulics. Can you explain how you came up with the Constr_1 and Constr_2. I'm having a hard time wrapping my brain around the purpose of those. Why the multiplication by 1.05 and 0.1?  Register To Reply

4. ## Re: How to limit Solver's choices

Let In and Bn be our Integer and Binary variables. We want to express a linear relation between them, so that when In > 0, Bn = 1, and if In = 0 then Bn = 0 as well.

We introduce two constants, m and M, and write the following constraints

In <= Bn*M
In >= Bn*m

We chose M to be greater than the max value In can get. We could use a very big number, but usually we want it to be as small as possible, in order to reduce the search space.
In your model, this value is found in cell S2, and depends on which part you select in the filter (A or B, 39 or 13). Then we multiply it for a small coefficient to be on the safe side.

In <= Bn*40.95

and forces Bn to be 1 if In > 0.
For the second constraint we follow the same reasoning, but this time we look for a very small value, i.e.

In >= Bn*0.1

and this equation forces Bn to be 0 when In = 0.

We can also look at the constraints from the opposite view.
When Bn = 0, we get In = 0 from the first equation. If Bn = 1, from the second equation we see that In must be "used".

It may seem very complicated, but what we are trying to translate is: "If a part number is chosen, then count it as 1".
Excel offers a number of ways to write this conditional statement in formulas (COUNTIF, SUMIF and so on), but the price we pay is moving away from a problem that can be solved with the Simplex (and Branch and Bound) algorithm, and entering the world of discrete optimization. Over there, things can get really hard.

If the number of variables (and their range) is small, we can use the Evolutionary engine, and hope for the best. In your problem there are many integer variables, therefore a mixed integer linear approach may be more rewarding.

HTH,

Francesco  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 