Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Multiple Solver Constraints in Solver

  1. #1
    Join Date
    10th September 2006
    Posts
    4

    Multiple Solver Constraints in Solver

    I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.

    What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.

    I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?

    Thanks,
    -Simon

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th December 2004
    Location
    Arizona, USA
    Posts
    819

    Re: Tricky constraint in solver

    Can you post a small sample workbook of what you are trying to accomplish?

    Thanks
    -marc

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Posts
    966

    Re: Tricky constraint in solver

    simonsays,

    Try the attachment. I don't know if will suit your purposes if your question is a scaled down example.

    Jim
    PS-Welcome to OzGrid...
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    10th September 2006
    Posts
    4

    Re: Tricky constraint in solver

    Thanks Jim, I looked at the worksheet you provided. Unfortunately I don't think it'll do the trick. You suggested as constraints:

    A5 <= max of desired range
    A5 = integer
    A5 >= min of desired range

    In the particular instance of the data you provided, it works - but only because the solution happens to be the max values of all three data columns. Presumably it would also work fine when the solution used all the minimums.

    My problem is that the solution (I'm going to be using many more columns and perhaps 200 rows when I do this for real) is almost certainly going to involve many intermediate values, and I require that they be drawn exclusively from pre-existing values in my data column. My constraint needs to limit A5 to the specific values contained in a specific range.

    I'm starting to feel like maybe Solver can't do this. Do I need VBA? I've been holding off on learning it for as long as possible (saying "nah, I don't need it, I'll just use solver and reeeeeally long IF statements") but this might be the last straw.

    Any ideas? Thanks again,
    -Simon

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th August 2006
    Posts
    41

    Re: Tricky constraint in solver

    Simonsays,

    I the following array formula in cell A7 for example and filled across.

    =MIN(ABS(A5-A1:A4)) confirm with ctrl+shift+enter

    If this formula returns zero then you mave a match with the required set of values. Set these formula cells equal to zero as a constraint.

    The problem is non-linear so you might need to play around with solver settings.

    hope this works,

    Paul
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    10th September 2006
    Posts
    4

    Re: Tricky constraint in solver

    Nice idea! I'll try that.

    -Simon

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    17th August 2006
    Posts
    41

    Re: Tricky constraint in solver

    Simon,

    (had to edit because I forgot a constraint, doh! )

    After playing around, I think its a bit much for solver to handle. You really need to keep things linear for solver to have a chance. I've reworked the solver model, its a bit more complicated but everything is linear so it should work. You might be waiting a while for a large problem instance though.

    good luck

    Paul
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Paul Corry; September 11th, 2006 at 10:02.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    10th September 2006
    Posts
    4

    Re: Multiple Solver Constraints in Solver

    That's a great solutions. Thanks. I'll apply it to my full model.

    The original idea like you said caused solver to blow a gasket. I'm not sure exactly what this linearity issue is. Could you explain it in simple language? Is it that solver can only find an answer when it makes small, discrete steps?

    -Simon

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    17th August 2006
    Posts
    41

    Re: Multiple Solver Constraints in Solver

    Hi Simon,

    Glad I could help, your problem was a fun little exercise. Here is a carsh course on linearity.

    if your variables are denoted X1, X2 and X3, a linear expression takes the form:

    aX1 + bX2 + cX3

    where a,b and c are constants.

    examples of non-linear expressions are:
    aX1^2, sin(X1), abs(X1), max(X1,X2), etc.

    Models which have only linear expressions have search spaces with smooth and predictable geometry, so given enough time Solver should be able to find a solution.

    Non-linear models have "bumpy" and unpredictable search spaces so Solver can easly get trapped, unable to continue its search. Often (but not always), a non-linear model can be transformed into a linear model by introducing new variables and constraints.

    The moral of the story is to try to use a linear formulation wherever possible.

    There's my carsh course in linearity, hope it made sense,

    Paul

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    9th June 2006
    Location
    The best city, in the best state, in the best country, in the known inhabited universe, Houston, TX
    Posts
    966

    Re: Multiple Solver Constraints in Solver

    simonsays,
    First you said:
    What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
    then:
    My problem is that the solution (I'm going to be using many more columns and perhaps 200 rows when I do this for real) is almost certainly going to involve many intermediate values, and I require that they be drawn exclusively from pre-existing values in my data column. My constraint needs to limit A5 to the specific values contained in a specific range.
    So, now I'm confused, did you try my solver set-up because I think it works for any values in the range as long as they are integers and there is only one solution (like you said at first)?

    Jim

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Solver With Constraints For Inventory Levels
    By blader_vt in forum EXCEL HELP
    Replies: 13
    Last Post: November 12th, 2008, 01:57
  2. Solver Macro Code With Constraints
    By tejas in forum EXCEL HELP
    Replies: 1
    Last Post: March 7th, 2008, 10:41
  3. Schedule Employees With Constraints With Solver
    By mominindy in forum EXCEL HELP
    Replies: 2
    Last Post: February 23rd, 2008, 08:16
  4. Setting Solver Constraints
    By scaredofsolver in forum EXCEL HELP
    Replies: 4
    Last Post: April 4th, 2007, 17:31
  5. Solver and Constraints
    By QuickDraw in forum EXCEL HELP
    Replies: 3
    Last Post: July 21st, 2005, 06:28

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
  •  
porno