+ Reply to Thread
Results 1 to 19 of 19

Solver does not maximize

  1. #1
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. Ive got a problem with the solver that I cant find the solution to, so Im hoping that any of you guys can help me.

    Im 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
    Attached Files Attached Files

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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

  3. #3
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    thanks for your input, Im beginning to see the problem now. But I cant 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 wasnt the case I get a target cell value of 2*10-176.

    Do you know any way to derive the needed constraints? Because I cant 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 Im not really interested in the coefficients of the variables, only the result in the target equation.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Solver does not maximize

    For scaling, you could just change the formula in U19 to =1E+40*PRODUCT(T19:T117)

    You still need to apply some constraints, e.g., the standard deviation must be > 0.
    Last edited by shg; 07-21-2011 at 03:04 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    The maths behind your problem is way over my head. Lets 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, dont know if this will be of any help but the best of luck to you in solving your problem.

    Alf

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    Tala om trollen .....

    Alf

  7. #7
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    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 Id 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. Dont know what this will result in, but its worth a try.

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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

  9. #9
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    Tack igen Alf

    I havnt 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 dont 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?

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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

  11. #11
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    I have attached the latest excel-file

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

  12. #12
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    Ive come up with a new excel-sheet, and would be glad for any help and assistance is possible. Below is a brief explanation to the sheet:

    Columns Q-Y are linked to the adjustable cells and my variables used in the model.

    An explanation of few of the columns linked to the adjustable cells is:

    O: an equation based on the first 2 adjustable cells and 1 variable
    P: the cumulative distribution function of O
    Z: This is the goal/target equation as shown in the attached picture.
    AA: This is the difference between the estimated inflation and the real inflation
    AB: This is the likelihood of obtaining this difference, which I want to maximize.

    My problem is two-folded:

    1) When I run the solver, the adjustable cells D and E never changes, even though they are linked to column O. Does anyone know how to connect everything so that the solver uses all the adjustable cells?

    2) Solver is also very sensitive to starting values of the rest of the adjustable cells. Does anyone know a way to figure out the global results instead of just the local?

    I know its a lot I ask for, but its really hard to work on this alone so I would really appreciate any input.

    I have attached the excel 2007-file and a picture of the model equation if you want to have a look at what I have done.

    Best regards,

    Clegane
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by clegane; 07-23-2011 at 01:00 PM.

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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.

    Dont know if this will be of any help to you but since I dont understand what you are trying to model (maths is not my strong point) its difficult to make meaningful suggestions.

    Alf

  14. #14
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    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.

  15. #15
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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.

  16. #16
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

    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

  17. #17
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    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

  18. #18
    Registered User
    Join Date
    07-21-2011
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Solver does not maximize

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

  19. #19
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,670

    Re: Solver does not maximize

    haha, no worries! Ive 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1