Constrained optimization using Solver tool - ERROR(?)

[Bushido88]

I am trying to do some constrained optimization in excel with the Solver tool, but I am encountering the following error:

"Solver encountered an error value in the Objective Cell or a Constraint Cell" - which is pretty ambiguous. I think I see where the problem is, but I'm not sure why its happening. This optimization problem contains 4 changeable parameters/variables - and one of them is returning a value of zero which is creating a #DIV/0! error.

The problem is as follows:

I am trying to find the values of e1, e2, s1, and s2 which maximize the function:

f = [(1/(1+P1+P1P2))*(-e1^2)] + [(P1/(1+P1+P1P2))*(R1-e2^2)] + [(P1P2/(1+P1+P1P2))*(R1+R2)]

where
P1 = [(e1-s1)/e1]
P2 = [(e2-s2)/e2]
R1 = [(e1+s1)/2]
R2 = [(e2+s2)/2]

Subject to the constraint that e1, e2, s1, and s2 are non-negative

A copy of the xls can be found here: http://docs.google.com/file/d/0BxzdaGm_pXBncEtNMFRUU0M5QXc

When the solver runs - I get the error message and e1 returns a value of zero - which creates a #DIV/0! error in calculating P1, and everything gets thrown off from there. I'm using the GRG NonLinear solving method.

In an attempt to get a non-zero figure for e1, I tried changing the constraints on e1, e2, s1, s2 from non-negative to ">= 0.0000001" but STILL e1 returns a value of zero?!

Perhaps I've set something up incorrectly? This is my first time doing anything like this in excel, and I'd love some insight on how to get this to work, or even if you guys could comment on my formatting in general that'd be great. I'm sure there's tons of room for improvement.

Thanks in advance, and don't hesitate to ask for clarification of any kind...

 

[Bushido88]

I'm still curious about the above..

I managed to run the solver on this problem and got non-zero values for the parameters e1,e2,s1 and s2 by writing out the equation in full referencing only cells c4-c7 rather than defining P1,P2,R1, and R2 and referencing cells c9-c12. (which is long and ugly)

HOWEVER, now I am now encountering the error "Objective Cell values do not converge" - does this suggest that the target cell has no maximum? how should I interpret this?

I know for a fact there is a unique solution to this problem. Why can't I find it? what am I doing wrong?

 

[Bushido88]

hmm seems I cannot edit my posts? just to make absolutely clear.. the problem is as follows:

I am trying to find the values of e₁, e₂, s₁, and s₂ which maximize the function:

f = [(1/(1+P₁+P₁P₂))*(-e₁²)] + [(P₁/(1+P₁+P₁P₂))*(R₁-e₂²)] + [(P₁P₂/(1+P₁+P₁P₂))*(R₁+R₂)]


where
P₁ = [(e₁-s₁)/e₁]
P₂ = [(e₂-s₂)/e₂]
R₁ = [(e₁+s₁)/2]
R₂ = [(e₂+s₂)/2]


Subject to the constraint that e₁, e₂, s₁, and s₂ are non-negative

 

[Bushido88]

bumpp

 

[shg]

The constraint you applied (>= 0.000001) is the same as the precision setting in the model, which means those values can go to zero, which makes the function blow up. So increase the precision, or increase the lower constraint.

When you do that, though, the function runs away. I have no insight as to what the function looks like; perhaps for a different set of initial values it will converge.

 

[Bushido88]

The constraint you applied (>= 0.000001) is the same as the precision setting in the model, which means those values can go to zero, which makes the function blow up. So increase the precision, or increase the lower constraint.

When you do that, though, the function runs away. I have no insight as to what the function looks like; perhaps for a different set of initial values it will converge.

Thanks for the response. I appreciate the guidance. To say I'm confused would be an understatement.

I changed the constraints on my variable parameters back to >= 0 and after playing around with the set of initial values - I have finally gotten the solver to return solution(s) for (e₁, e₂, s₁, and s₂) which satisfy all constraints and "maximize" my function, but something is definitely wrong.... I can run the solver in another worksheet and get different, but similar results - or I can change the set of intial values and get a solution thats radically different from the others.

I don't see the method to this madness. Why does changing the initial values of my variable parameters affect the optimization?! Why am I receiving a different solution nearly every time (although sometimes the solutions are very close, within decimals of eachother). It makes no sense to me.

 

[shg]

Why does changing the initial values of my variable parameters affect the optimization?!

As I said, I don't know what your function looks like, but simple polynomials can have multiple local minima and maxima, and the sine function has an infinity of them. The one you get depends where you start looking.

 

[shg]

BTW, in this expression

[(1/(1+P1+P1P2))*(-e1^2)] + [(P1/(1+P1+P1P2))*(R1-e2^2)] + [(P1P2/(1+P1+P1P2))*(R1+R2)]

-e1^2 is the same as e1^2 because of Excel's precedence of operators (-5^2 is 25, not -25)

And with some rearrangement, the whole expression can be simplified from

=((1/(1+$C$9+($C$9*$C$10)))*(-$C$4^2))+(($C$9/(1+$C$9+($C$9*$C$10)))*($C$11-$C$5^2))+((($C$9*$C$10)/(1+$C$9+($C$9*$C$10)))*($C$11+$C$12))

to

=(C9 * (C11-C5^2) + (C9*C10) * (C11+C12) - C4^2) / (1+C9+C9*C10)

Solver can find a maximum for that.

 

[shg]

... to

=(C9 * (C11-C5^2) + C9*C10*(C11+C12) - C4^2) / (1+C9+C9*C10)