Solver doesn't find the best solution

[waldymar]

Guys,

I have a spreadsheet with many calculations. I need to maximize my equation with some restrictions. As my equation is not linear, I'm using evolutionary option in solver. It finds me a solution, but it's not the best (maximal value). Is there any secret how solver can find the best solution, actually?

[MrShorty]

I don't think you have provided enough information to provide a solid answer. It will depend on what is happening that is preventing the algorithm from finding the maximum.

I am not familiar with evolutionary algorithms, so I quickly reviewed Wikipedia's article: https://en.wikipedia.org/wiki/Genetic_algorithm A few ideas from the "limitations" section:

1) "The "better" solution is only in comparison to other solutions. As a result, the stop criterion is not clear in every problem." -- Perhaps there is something about your objective function that makes it difficult for the algorithm to see the maximum. This is more math than Excel, but you may spend some time with your objective function to see if it can be improved to make the maximum easier to find.

2) As with a lot of optimization algorithms, "In many problems, GAs may have a tendency to converge towards local optima or even arbitrary points rather than the global optimum of the problem." Basically, the algorithm finds a peak, but it cannot see that there is a peak three ridges over that is higher. You have not given any indication if this is a possibility in this case. If this is the case, many times simply rerunning the algorithm with a better starting value(s) (close to the desired maximum) will allow the algorithm to find the maximum value.

3) Can we assume here that the algorithm is terminating due to normal convergence critieria and not "max time" or "max iterations"? It might be worth checking to see what is causing the algorithm to terminate. If it is terminating due to anything other than "Solver has converged to the current solution", then examine why it is artificially terminating.