+ Reply to Thread
Results 1 to 4 of 4

Setting the solver properly

  1. #1
    Registered User
    Join Date
    06-27-2016
    Location
    -
    MS-Off Ver
    -
    Posts
    3

    Setting the solver properly

    Hi all,

    I am trying to use the solver to maximize a function.

    Attached you will find the model.

    1. Range A1:N10 is given
    2. Range A17:O26 is the new range
    3. Variables are in D18:D26
    4. Variable 14 (O18:O26) contains the equations to consider as defined in cells I33:I40 where x in cell I33 would be equal to (D18/D2-1) -> cell O18
    5. The objective : maximize the sum of rows for variable 11 - L18:L25
    6. The constraints are defined from row 48 (e.g. B50 <= C50 and so on depending on upper or lower bound)

    I am having trouble in setting up the solver to maximise this function given these constraints but can't find what is the issue.

    Many thanks in advance for your help.
    Attached Files Attached Files

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

    Re: Setting the solver properly

    I do not understand exactly what or how you are trying to optimize this. If I may request clarification:

    3. Variables are in D18:D26 -- Does this mean that you want these to be your "By changing" cells? I note that changing these cells has no impact on L18:L25 (which you claim is your objective function or your "Set Target Cell" -- did I misunderstand?), so it would seem impossible to maximize L26 if there is no relationship between D18:D26 and L26. Are there supposed to be formulas that connect these cells?
    4. I did not understand at all how O18:O26 fits into your model and optimization.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    06-27-2016
    Location
    -
    MS-Off Ver
    -
    Posts
    3

    Re: Setting the solver properly

    Thanks for your reply MrShorty.

    Attached a clearer version of the file (I hope).

    - My "by changing variables" are cells A18:A25
    - The result to maximize is indicator 2, cell 26 (sum of cells C18:C25)
    - The constraints are in range B66:B69

    Many thanks in advance for your help.
    Attached Files Attached Files

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

    Re: Setting the solver properly

    Among the things I see:

    A18:A25 are blank (which Solver will interpret as 0) is this your best guess for a starting value? I would probably try your upper or lower bounds (C49:C56 or F49:F56) or something in the middle (all =0.5).
    Trying upper and lower bounds shows that the lower bounds will be out of range for other criteria, and seems to represent a minimum. The upper bound values seem to be a maximum and most constraints are met. Is Solver even needed if the upper bound is the maximum? Are there scenarios where the upper bound is not the maximum?

    I note that, as presently programmed, H48 is always "-", because D26 always evaluates to an error due to the text string in A26. I don't know what this constraint represents, nor what the computation in D26 is supposed to be, but something does not seem right here. Perhaps this is the main source of your difficulty -- getting an appropriate non-error expression in D26.

    Once all of the constraints are being calculated correctly, it seems like the Solver model should be:
    Set target cell: C26
    To a maximum
    By changing: A18:A25
    Subject to the constraints as listed.

+ 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. Replies: 1
    Last Post: 04-27-2016, 03:05 PM
  2. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  3. Solver Help: Setting a Constraint to Allow Two Values
    By Schooch47 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2014, 09:05 PM
  4. Can't start Solver. Error message says Solver.xlam already open.
    By DaveHills in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 10-21-2012, 11:02 AM
  5. Setting up Solver constraint
    By Jstewart304 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-23-2012, 01:36 PM
  6. Replies: 0
    Last Post: 05-14-2010, 03:41 AM
  7. Setting Solver Reference-programmatically
    By davidm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2006, 08:15 AM

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