Solver in Excel with multiple constraints

[ugalskov]

Hi Forum,

I am trying to maximize a function in Excel with multiple constraints. However, the Solver does not seem to be able to solve this particular optimization problem. I might have overseen something.

[MrShorty]

It seems to work (syntactically) just fine for me. What do you mean by "Solver does not seem to be able to solve this particular optimization problem"? Is Solver doing nothing? Is Solver finding a solution, but you believe there is a better solution? Is Solver producing an error message?

[ugalskov]

Thanks for your reply MrShorty. I was hoping for the solver to find a better solution, i.e. get a higher value of the target cell

[MrShorty]

Are you certain that a better solution exists? If so, do you know what inputs will lead to that solution? I realize that the goal is to have Solver tell us what the solution is. However, in order to test, debug, and develop something like this, I find that I need to test the spreadsheet on a problem where I know what solution Solver should find, so I will know when Solver comes to the wrong solution.

[ugalskov]

Yes I am certain that a better solution exists. The solution should be in the range of 0.60-1.

[MrShorty]

Do you have any ideas what inputs will give a result in that range? I have not come across a combination that gives that high of a result, yet.

[ugalskov]

I have either, actually I cant come above 0.439. But the solver should be able to optimize this setup, in my mind.

[Alf]

Clearing the range F10:F23 and running solver with the GRG Nonliners metode I get a value of 0.44 for K9

Doing a rerun using the Evolutionary metode and setting the "Maximum Time without improvement" to 15 I get a value of 0.45455 for K9.

I don't think you could do much better than that based on your constraints.

Alf

[MrShorty]

Solver cannot do the mathematically impossible, though. We need to know that there is a better solution before we can figure out how to help Solver find that solution.

I am not convinced that there is a solution in that range with the constraints as they are given. If I relax the constraints so that weight can be 0.02 to 0.12, I can get the value up to 0.6, but not constrained to 0.05 to 0.1. I cannot prove it (and I don't see a reason to go through the algebra to get the final proof), but it looks to me that 0.6 (or more) is not possible with the current set of constraints.

[ugalskov]

Thanks for the reply Alf, and MrShorty. I am convinced that it is working correctly. If I try to relax the constraints then I get similar results as you MrShorty.