Excel Solver issue Evolutionary

New Contributor

I am trying to find and optimized basket of bonds where the block in X to AD highlighted in red border are the constraints as well as integer and between 0 and 8 constraints for the changeable variables of C12 to C109.

I randomly found a very good basket running it with only 1 part of the minimizing I am looking at, but now it seems to keep getting stuck in some weird local minimum and far from the solution if I try add to the objective function.  I am not sure if I am trying to set up the problem wrong, but if anyone can help point me in the right direction on what I am doing wrong it would be amazing.

If I can add anything or expand on what I am trying to acheive, please let me know and the filled red block above the constraints are the various parts I am trying to add to get an optimal basket from.

3 Replies
I actually can't seem to attach any excel files ...
It is common that new users can't upload but you should be able to share it in OneDrive or SharePoint or other document sharing service and include a link. Maybe your text will make sense once one looks at the file itself, but I think you could explain the situation and goal a bit more also.



Local minimums (maximums) are a common problem with any goal-seeking algorithm.


From a Solver webpage (click here  ``The GRG Nonlinear Solving method, like most nonlinear optimization methods, normally can find only a locally optimal solution to a nonlinear, non-convex problem.  Solver includes a multistart method that can improve your prospects of finding a globally optimal solution for such a problem.  The basic idea of the multistart method is to automatically run the GRG “local Solver” from different starting points, reaching different locally optimal solutions``.


I don't know if the multistart feature works with the Evolutionary method.  My guess is:  it does not.



@MillerMark39  wrote:  ``if anyone can help point me in the right direction on what I am doing wrong it would be amazing``


We would need to see the Excel file in order to be specific.  Refer to @mtarler's response.  I like box.net/files; others like dropbox.com.


And no approach is guaranteed to work.


But generally, I try to add reasonable constraints on various values, including but not limited to the "by changing" cells.


And to that end, I might add additional calculations that can further constrain the solutions.  For example, the SSE or absolute AVEDEV must be between "this" and "that".