Problem using the "solver function".

**MathiasH** · 11-16-2011, 04:12 PM

I want to maximize the expected return on an investment. To do this i have created some parameters which have an effect on the outcome, and if they are adjusted optimally they should make it possible for me maximize my expected return.

I throught I would be able to use solver to adjust these parameters, however while the solver function tells me that it has come to a result, it doesn't change the value of the parameters.

And this is weird as the values that I am currently using in the parameters are randomly chosen, and definitely aren't optimal. I can manually change the numbers of the parameters and they will change the the expected return.

I suspect that the problem may be due to the fact that there aren't nessarily one specific optimal solution to each parameter. There may be more than 1 value that is optimal. But I am not sure whether this is the actual problem, or even how I can fix this problem.

Any suggestions?

**davegugg** · 11-16-2011, 04:31 PM

Can you post your workbook and describe your expected solution?

**MathiasH** · 11-17-2011, 11:01 AM

I can't attach my excel file because its too large (if that is what you mean by workbook) (?).

But do you think the problem may be due to the fact that the solver function doesn't work if the variable that is too be adjusted has more than 1 optimal value?

**davegugg** · 11-17-2011, 11:18 AM

If it has more than one (ie more than one local minima/maxima are also the absolute minima/maxima), the solver should just display whichever is found first.

I'd guess you need to change your criteria or assumptions in some way. I'd be glad to help, but you'll need to post a sample workbook. Can you get rid of all the unneeded info and then post?

**MathiasH** · 11-17-2011, 11:50 AM

I uploaded a version of my workbook.

If you go to sheet "2007-2008 kampe", I want to maximize"K637" by changing the variables in "D23", "d24" and "d25".

**davegugg** · 11-17-2011, 12:11 PM

Cell K637, which you are trying to solve for, is dependent upon B637:I637. B637 is dependant upon B633:B635. B635 is dependant upon B622 (and some other cells). B622 uses an IF() function, so your model is non smooth and discontinuous. You can't use Excel's built in solver for non-linear problems (see here).

I would also say your model is very complex, and even without those discontinuities I wonder if the Solver would be able to work.

**MathiasH** · 11-17-2011, 03:12 PM

Thanks for your help.
Yes i know its very complex, and I guess solver then isn't able to figure out a solution. But do you think there are other ways to get to a solution? I mean I could change every variable manually and the expected outcome (celler k637) would change, so I would just think that excel had some clever function to fix this problem (?).

**davegugg** · 11-17-2011, 03:49 PM

Optimization (or operations research) is a very complex field - I took two classes on it in college, and we still only scratched the surface. If you are able to turn your problem into a linear optimization problem, you could probably solve it. I'd start either by writing out an equation to your problem, or arranging your spreadsheet in a much more intuitive manner, where formulas are grouped by what cells they depend on and what cells depend on them.