Solver does not maximize

Hello everyone,

This is my first time here, and I hope to find any friendly spirits that can help me. I would be very grateful. I´ve got a problem with the solver that I can´t find the solution to, so I´m hoping that any of you guys can help me.

I´m trying to maximize a likelihood function. I have a set of adjustable cells and a target cell. Furthermore, I have 6 columns. Each with a different formula that is dependent on the adjustable cells, and directly or indirectly linked to the target cell.

In 2 of the columns I use the formulas "normdist(x;0;1;true)" to get the cumulative dist. function and "normdist(x;0;1;false)" to get the probability mass function. In the last column I use the formula "product" to get the likelihood that I wish to maximize.

What I hope to do is to change (maximize) the adjustable cells so that the 6 columns receive their optimal values so that my likelihood function is maximized.

The problem is that when I use the solver, it returns an answer: "Solver found a solution. All constraints and optimality conditions are satisfied."

However, nothing changes in the adjustable cells (and hence nothing changes in the 6 columns). I have tried to choose the pre-set values in the adjustable cells so that the result in the 6 columns are close to what I assume to find (and in 2 cases what other research have found).

Is there anyone who have any thoughts on what might be the problem?

I have attached the excel 2007-file I you want to have a look at what I have done.

Best regards,

Clegane

Looking at you model I would say Solver needs a driving force i.e. you have not defined any constraint in your model.

The present value in your object function (target cell) is quite small 3*10¨-40. Maximizing this value what would be the expected result?

If the difference between the preset value and the max value is too small Solver will not do any further optimization. Then you have to start scaling your values.

HTH

Alf

thanks for your input, I´m beginning to see the problem now. But I can´t figure out how to scale the numbers without messing up the whole purpose of the estimation.

All the variables are in percent. If I write it in 7,00 instead of 0,07 then the normal distribution estimation in column P (the first normdist equation) gets screwed up and I only get the values 1 (since the values are now on the very far right side of the normal bell curve). Even if that wasn´t the case I get a target cell value of 2*10¨-176.

Do you know any way to derive the needed constraints? Because I can´t really find any in the equations I have (except that I have a vague memory that the x in normdist(x;0;1;false) should be less than 1). This is because I´m not really interested in the coefficients of the variables, only the result in the target equation.

The maths behind your problem is way over my head. Let’s hope the moderator shg takes an interest in your problem. In my opinion he is second to none in this forum regarding mathematical and Solver problem.

Still playing around a bit with your model and setting a constraint on V4:V15 <= 10 Solver changed the value on the obj. func. to 3,05119 *10-40

Setting V4:V15 >= 0 and V4:V15 <= 10 changed obj. value to 3,05211*10-40 and finally setting V4:V15 >= 5 and V4:V15 <= 10 changed obj. value to 0.

Well there you are, don’t know if this will be of any help but the best of luck to you in solving your problem.

Alf

Tala om trollen .....

Alf

Thanks for your help, shg and Alf. I will try the constraint on stdev and try to search for some more. If you have some more knowledge I´d be very grateful for your insights.

For the last couple of hours the solver has been calculating the same thing but now with unlimited iterations and a lot more 0:s in precision and convergence. The target cell value is slowly getting higher. Up to ¨39 from ¨40.. lol. Don´t know what this will result in, but it´s worth a try.

The makers of Solver can be found at http://www.solver.com/

They will not give any support on the standard Solver models i.e. Solver bundled with Excel but you can download the more advanced models and use it fore free for a limited time. As they wish to sell these models I would assume that you will get a bit of support as well if you were to try out one of these models.

In Excel 2010 there are two Solver models. The GRG Nonlinear and the LP Simplex engine. I do think they can handle bigger matrix(es?) than Excel 2007 and may be faster. Perhaps worth an upgrade?

Still in the end it boils down to having a proper model with the right constraints.

If you have more questions about Solver I would recommend starting a new tread as a new post attracts more attention than an “old” one. You could always add a link to your previous post as additional information.

Alf

Tack igen Alf

I havn´t found a solution yet, but I have managed to get the solver to change cell-values in the adjustable cells, except for the first 2 (V4 and V5). These two cells change the the normal distribution equations in column O and P.

Do you guys have any idea to why they don´t change while the rest of the adjustable cells do change? My idea is that it may be a broken link that I am unaware of, or a link that does not work!?

Furthermore, the results are very sensitive to the starting values in the adjustable cells. I have found many different solutions depending on starting values. Do any of you guys have any idea on how to find the global maximum/optimum?

Do any of you guys have any idea on how to find the global maximum/optimum?

Sorry, no clue but why don't you upload your workbook with your last Solver setting so we can have a look at it.

Alf

I have attached the latest excel-file

The constraint on standev (the x) did wounders, as the scaling of the target cell.

There is something strange with your model. If I change the result (range AD4 to AD15) a bit and do a Solver rerun I get a Solver solution in a matter of seconds. Not enough constrains perhaps?

If I on the other hand change the target cell range i.e. AB19:AB117 to say AB19:AB40 Solver really makes an “effort” to reach a solution and runs for about 25 – 30 seconds before it stops (Solver has converged to the current solution). Could it be that your model is too big or to complex for the standard Solver?

In the range AB41:AB61 Solver ran for about 120 seconds before reaching a solution (converged again) and finally in range AB61:AB81 it only took 5 seconds before Solver converged.

Don’t know if this will be of any help to you but since I don’t understand what you are trying to model (math’s is not my strong point) it’s difficult to make meaningful suggestions.

Alf

Thanks for your help, its appreciated! =)
It could be that its to many variables, and perhaps your right about the length of the time series as well. I have actually no idea of the limitations of the solver and excel in these situations. Unfortunately, the model is useless unless I can model it over a reasonably long time length. The good part is that I have found many solutions at this point, the flip side is that there are to many solutions to make the whole model and its further equations work.

It could be that its to many variables, and perhaps your right about the length of the time series as well.

Perhaps it is so that's why I recommend you register at at http://www.solver.com/ so you can download one of their more powerful Solver platforms and try it out to see if that helps.

Alf

Ps If you crack this problem could you please report back as I like to learn as much as possible about Solver.

Thanks Alf for your idea to download a solver platform. So far it has not generated any solution, but its certainly more powerful at least. It says that my model is a "non smooth model". Honestly I have no idea what that means, but I guess that it might be something lacking in my model specification. I will get back on the issue once I know more..

/Clegane

There is a bit of information on this page. http://www.solver.com/tutorial6.htm#...ation problems

I would recommend that you get in touch with the staff at "Solver.com" to get them to take a look at your model and ask for advice how to solve this problem.

If you do get help I think that by now you owe me a cafe latte taken at one of the more fashionable coffee shops around Nytorget.

Alf

haha, no worries! I´ve actually already mailed them. If you come up to Sthlm some day, let me know!

haha, no worries! I´ve actually already mailed them.

Good, lets keep fingers crossed that they can help you.

For your information: The standard Solver will normally not work with nonsmooth functions like IF, MIN, MAX, ABS, AND, OR, COUNTIF and NOT.

Looks like NORMDIST also is a nonsmooth function. This is probably what causes your Solver problem.

Sometimes a nonsmooth functions can be replaced by using a binary variable.

Hopefully the Premium Solver Platform can handle nonsmoot functions.

If you come up to Sthlm some day, let me know!

How about next week?

Alf