+ Reply to Thread
Results 1 to 5 of 5

Solver not finding the solution

  1. #1
    Registered User
    Join Date
    03-24-2015
    Location
    Vilnius
    MS-Off Ver
    Excel 2013
    Posts
    2

    Solver not finding the solution

    Im trying to choose the highest number from a list and Solver keeps posting the same number. It might have to do with the indirect function that Im using.
    Book2.xlsx
    Attached Files Attached Files
    Last edited by Domux; 03-24-2015 at 10:51 PM.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Solver not finding the solution

    there is some work to do in g2
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-24-2015
    Location
    Vilnius
    MS-Off Ver
    Excel 2013
    Posts
    2

    Re: Solver not finding the solution

    You see, Im going to use the price one later on to constraint the choices and add choosing not only one product, so using the maximum wont work. I have updated the OP file to reflect that.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP, 2007, 2024
    Posts
    16,369

    Re: Solver not finding the solution

    Between the Solver and the INDIRECT() function, you are correct, this is a very poor setup for a GRG-nonlinear/Newton-Raphson (assuming you are using the default Solver algorithms) type algorithm. The chosen objective function has so many problems with it, I'm not even sure where to begin. The ROUND() function makes it "discontiinuous", and the INDIRECT() function makes it very random. The default algorithms just will not know how to handle this kind of function.

    1st suggestion, you might try an Evolutionary algorithm (available in 2010 and later). An evolutionary algorithm may do better, I don't know, and I can't test it on my older version of Excel.

    If that fails, I would probably suggest a complete re-think of the spreadsheet and the optimization. Perhaps you will want to completely reformulate your "objective function" (the objective function the function, such as the one in H3, that you are trying to optimize).

    Or perhaps you completely take Solver out of the picture. I do not know how representative this simple spreadsheet is of your actual problem. In your sample spreadsheet, there are really only 3 possible values for G2: 0, 1, and 2. It might be easier and faster to arrange the spreadsheet so that it computes the result at all three possible inputs, then a MAX() (or equivalent) function can extract the maximum values from the three possibilities. Even if the sample spreadsheet is an oversimplification, it might still be easier to compute your desired result at 10 or 100 possible inputs and extract the maximum than it will be to re-think the objective function.

    One way or another, I would suggest the the approach you are taking is not very suitable for the real problem at hand. If Solver's evolutionary algorithm cannot solve the problem as is, I would suggest that you will need to come up with a completely different approach to the problem.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,784

    Re: Solver not finding the solution

    Change solver model from "GRG NonLinear" to "Evolutionary" and run Solver.

    Alf

+ 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. Solver - Finding the next best solution
    By mattdh12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2014, 10:41 AM
  2. Replies: 11
    Last Post: 10-30-2013, 02:11 PM
  3. Solution With Solver
    By Kumara_faith in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2007, 08:00 AM
  4. Solver: no solution
    By Peter1999 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2007, 03:57 AM
  5. Solver solution hessian
    By PandS in forum Excel General
    Replies: 0
    Last Post: 02-01-2006, 11:10 AM

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