Forum Discussion
Excel Solver issue Evolutionary
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
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".
- 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.
