+ Reply to Thread
Results 1 to 4 of 4

Help with Solver

  1. #1
    Registered User
    Join Date
    11-07-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    72

    Help with Solver

    Problem: I can't figure out how to setup Solver to try and attempt to come up with a solution.

    Objective: Get D11 as close to 0.000 as possible.

    Variable cells: E11:E40

    Constraints:
    E11:E40 <=10
    E11:E40 >= 1
    E11:E40 = integer

    The easy solution on my worksheet is to change E34 to 4 and E35 to 5. This would make D11 0.000. However, the long term goal is that cells F11:F40 will have random dollar amounts and I'll want excel to distribute numbers 1-10 to E11:E40 to make the percentage distribution to each number as close to their assigned percentages as possible.. which the closer Solver gets, the closer to 0.0000 D11 will be. See screenshot here of my solver setup: TdbYXOw.jpg

    Any help on this is much appreciate. Thank you for your help.
    Attached Files Attached Files

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

    Re: Help with Solver

    You should set cell D11 to Min, not to zero.

    Since your problem is non-smooth, both GRG and Evolutionary engines will try to find what they see as "best" solution, but it may be just a local minimum.
    Starting from different points may help you get a better solution.

    I have set up your model switching to Evolutionary and changing slightly the standard options. As you can see, cell D11 is very close, but not exactly, zero.

    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
    11-07-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Help with Solver

    Thank you for your response. I've been slammed with work, so no time to test it. Should have time this weekend to review. Thanks again and sorry for not responding sooner. I will update this thread after I tinker with it.

  4. #4
    Registered User
    Join Date
    11-07-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    72

    Re: Help with Solver

    Your changes made the Solver do what I needed. Thank you for your help. It's greatly appreciated.

+ 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: 7
    Last Post: 12-31-2016, 12:29 PM
  2. Replies: 1
    Last Post: 04-27-2016, 03:05 PM
  3. Replies: 0
    Last Post: 07-20-2014, 12:45 PM
  4. macro not keeping solver solutions when solver is successful
    By jimmypants in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2014, 01:45 AM
  5. 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
  6. solver macro + simulation code + not updating solver values
    By sabinemaria in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2012, 11:37 AM
  7. Interesting Solver problem (Solver encounters an error)
    By MrShorty in forum Excel General
    Replies: 3
    Last Post: 12-22-2005, 06:55 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