+ Reply to Thread
Results 1 to 4 of 4

How to limit Solver's choices

  1. #1
    Registered User
    Join Date
    01-27-2020
    Location
    Tulsa,Ok
    MS-Off Ver
    365 ProPlus
    Posts
    17

    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.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    283

    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
    Attached Files Attached Files
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  3. #3
    Registered User
    Join Date
    01-27-2020
    Location
    Tulsa,Ok
    MS-Off Ver
    365 ProPlus
    Posts
    17

    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?

  4. #4
    Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    283

    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.

    The first constraint then reads

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to change the max time limit for Frontline Analytic solver in VBA?
    By Trist.B in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-25-2020, 05:45 AM
  2. Replies: 1
    Last Post: 11-08-2017, 03:16 PM
  3. Solver Function - Allocating Choices
    By mlukich in forum Excel General
    Replies: 2
    Last Post: 08-26-2015, 08:58 AM
  4. How to limit the number of choices in multiple drop-down lists?
    By Stefan48 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-16-2013, 04:15 PM
  5. Solver Time Limit
    By SDruley in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2011, 11:45 AM
  6. Replies: 0
    Last Post: 06-02-2009, 10:27 AM
  7. Is there a way to limit the content of a cell to 5 choices?
    By ChrissyG in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-01-2006, 03:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1