optimization problem - solver falling to get correct answer

Hey,

I have a cell (D21) with a value that, connected by some simple calculations in other cells, is dependant on three, variable cells (not containing formulas).

When I keep the two of the three variables constant and run the solver in order to optimize the value of D21, the solver fails to find the correct solution (but finds a solution though).
I have a hard time to see why it fails to find the correct solution, and it is highly appreciated if someone can help me with getting around this problem in some way.

Please take a look at the attachment, which shows the problem more clearly.

Thanks in advance for your help

The value of your target cell is oscillating and Solver can't cope with that.

Doing a manual input MA(x) going from 0 to 10 in steps of 1 change the value in D22 to:

1.123, 1.523, 0.699, 2.290, "#DIV/0!", 2.269, 2.807, 2.712, 2.640, 2.902, 2.464

So if the solver run starts with C2 = 0 and incrementing that Solver will find a maximum for D22 where C2 = 0.6 and the value of D22 is
1.523. Increasing C2 to 0.7, 0.8, 0.9 and 1.0 will not change the D22 value it will still be 1.523

Changing C2 to 1.1 will change the value of D22 to 1.081 so as far as Solver is concerned the maximum value of D22 is 1.523 and this is reached when MA(x) = 0.6

If you add a constraint to Solver i.e. C2 >= 8 then the Solver solution will give a D22 value of 2.902 with a C2 value of 8.6

So inside a range where your target value goes from a minimum to a maximum Solver will work but if you have two or more minimum/maximum values Solver will find a solution based on your starting point i.e. value of C2.


Alf

Thank you so much for the answer! But is there really no way of getting around the problem? I mean is there no way of making the solver search for solutions further away than just e.g. to 1, when there is a local maximum at 0,6? Or any other solvers available that can do the job...
The entire set up I made is dependent on the solver function so it would be such a shame if the effort I put in is wasted :/

You could check this link http://www.solver.com/
and see if one of their more advanced Sover model could be used.

You will have to pay for any of the advanced models but they can all be intergrated with Excel. I think you can have a free download of a model to test for two weeks after that you have to pay to use it.

Alf

Disclosure: I am unable to download or view your file (not sure how much difference it would make) so I'm only commenting on what is presented here.

In many ways, your question is now really more about numerical methods than about Excel (though, once a suitable numerical method is chosen, we will need to know if it is already incorporated in Solver or not). In my experience, Excel's Solver has two basic options for numerical method: Newton Raphson (default) and an evolutionary algorithm. Can I assume you are using the default NR method? How familiar are you with this method? If you are unfamiliar with the NR method, this would be a good time to research it enough to get a basic idea of how it works.

If you are using the NR method, you should be aware that, as Alf mentioned, the NR method is very dependent on the initial guess fed into the algorithm. Many programming examples I've seen will overcome this limitation by feeding several starting points into the algorithm, then running the calculations in parallel. Excel's Solver can't do this by itself, but with some additional programming, you should be able to program the spreadsheet to try several starting points. One way or another, if you are going to use the NR algorithm on a model that has multiple solutions/minima, then the programmer or the user has to be able to choose a suitable starting point so the algorithm will converge on the most appropriate solution.

Have you tried the evolutionary algorithm? I don't have much experience working with evolutionary algorithms, and none in working with Solver's EA, so I'm not all that sure how it is implemented. Again, if you are unfamiliar with EA's, you should probably do a little research so that you understand the basic idea behind them. An EA might have an advantage in this case over the NR method because it uses multiple starting points naturally. It would all depend on how broadly Solver chooses those "seed" values whether it would be able to locate the correct solution/minimum when multiple possibilities exist, but it is possible that it will be better able to chose a global minimum over a local minimum. EA's are computationally much more intense than the NR algorithm, so this possible improvement comes at the cost of computing power.

That said, the short answer to your question, "is there no other way?" is that I believe that you can get this to work. It may require a better understanding of Solver's available algorithms (and numerical methods in general) and how these methods can be programmed to handle the problems presented when multiple minima/solutions are present.

@MrShorty: First of all, thanks for the most useful reply, and a pity you can't open the file, but seems that Alf and others can, so not sure what to do about it.
You're absolutely right regarding my use of NR and of my lacking knowledge of the two approches. I'm looking into it at this point, and I think the way to get around the problem is indeed by applying more entrypoints as you suggest. The attached file shows a simplyfied edition of my problem, which has three variables, but as they are only defined in relatively narrow ranges, a rather small number of entrypoints should be good enough to find the correct optimum. I think my biggest problem will be that of programming the thing at the end, but I'm now down to searching the forum for related threads. If you have any suggestions of where I might find the structure of a such code that I'm looking for or any other suggestions that might be useful, I would love to hear it - otherwise thanks again for the help and I'll probably be back with a new thread when I run into trouble sooner or later