+ Reply to Thread
Results 1 to 7 of 7

Excel Solver Less Than or Equal To Constraint

  1. #1
    Registered User
    Join Date
    03-16-2016
    Location
    indiana, US
    MS-Off Ver
    2016
    Posts
    33

    Excel Solver Less Than or Equal To Constraint

    Hello all,
    I have some vba code to solve my system. Two of the constraints require variable values to be less than or equal to another cell value. However, for some reason solver always forces the variables to be equal to the other cell value instead of allowing it to be less than as well.
    In the code below, the Var7 and Var8 are the variables forced to be equal to ConstraintValue2 instead of allowed to be less than. Anyone have any tips or ideas? Thanks in advance.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,891

    Re: Excel Solver Less Than or Equal To Constraint

    I don't see anything in the code itself that would cause Solver to explain your question. My guess is that something in the mathematical model you are working with causes Solver to conclude that the minimum value for Result2 is always at the maximum values for Var7 and Var8. Have you analyzed your problem enough to know whether this is true or not? I don't think this will be something we can analyze on this side of the internet (unless you share the mathematical model you are trying to solve).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Excel Solver Less Than or Equal To Constraint

    Do CostraintValue2 or ConstraintValue2 change in the course of the solution? They are only evaluated once.

    Notice you have two different spellings.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    03-16-2016
    Location
    indiana, US
    MS-Off Ver
    2016
    Posts
    33

    Re: Excel Solver Less Than or Equal To Constraint

    shg,
    Sorry that was just a typing error when I was changing the names to post on here. Those two should be the same. Their values do not change in the course of the solution.
    MrShorty,
    There shouldn't be anything in the model that would cause solver to think that, but I will go through it and see if I notice anything.
    Thanks

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Excel Solver Less Than or Equal To Constraint

    BTW, in SolverAdd, FormulaText is redundant to Relation, as is EngineDesc to Engine in SolverOK.

  6. #6
    Registered User
    Join Date
    03-16-2016
    Location
    indiana, US
    MS-Off Ver
    2016
    Posts
    33

    Re: Excel Solver Less Than or Equal To Constraint

    Follow-up:
    I looked into the program and found two errors. The first was the way I was referencing cells on a different sheet. Solver didn't like that for some reason. The second error was the greater than or equal to constraint. The way the mathematical model works solver should have set the greater than or equal to variable (Result1) to the lowest value possible in order to minimize Result2 the overall objective of the solver. However, since there was no upper bound on Result1 solver instead choose to maximize Var7 and Var8 which resulted in Result1 being higher than necessary. I created a cell to calculate the error in Result1 and constrained it to zero and that fixed my issue.

    Thanks for the replies.

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: Excel Solver Less Than or Equal To Constraint

    Yep, they all must be on the same sheet.

    Also, the SolverOK statement should appear before the SolverAdd statements.

+ 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. Excel Solver: adding ratio as a constraint
    By candirt in forum Excel General
    Replies: 7
    Last Post: 12-07-2015, 05:09 PM
  2. Excel Solver Constraint Help
    By BauceArj in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-14-2014, 08:47 PM
  3. EXCEL Solver: Constraint equals one of two values (0 or 100)?
    By davidmummery1989 in forum Excel General
    Replies: 1
    Last Post: 01-14-2014, 06:54 AM
  4. [SOLVED] Excel Solver Constraint Question
    By Verna in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2012, 04:33 PM
  5. Excel Solver- Ratio Constraint
    By Patriot393 in forum Excel General
    Replies: 1
    Last Post: 06-12-2011, 08:55 PM
  6. Excel Solver: Constraint Specification
    By tonisama in forum Excel General
    Replies: 6
    Last Post: 11-16-2010, 05:00 PM
  7. Solver : add constraint "not equal to"
    By vichuda_0103 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 04-22-2009, 05:09 PM

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