Hello;

I would very much appreciate your help in the following relatively simple
non-linear optimization problem.

1) By applying Solver:
.....Set target cell: E18 to max....(E18 represents dependent variable E)
.....By changing cells: J18 ..........(J18 represents independent variable J)
.............................: P18...........(P18 represents independent
variable P)
.....Subject to constraints: B18=1
.....................................: J range 0.05 to 1.40
.....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation of the
problem.
For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on the
changing combinations of J and P and settles for the combination that
produces max E in comparison with other combinations and subject to the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think, something like:
...Solver1: for each tried value of P, say, P1 in the range 0.50 to 1.50
...Set target cell: E18 to max.....(E18 represents dependent variable E)
...By changing cells: J18 ...........(J18 represents independent variable J)
...Subject to constraints: J range 0.05 to 1.40
..............(solution: J1, max E1 at each P1)
.....Solver2:
.....Set target cell: B18 = 1
.....By changing cells: J18 ..........(J18 now represents the new variable J1)
.....Subject to constraints: J1 range 0.05 to 1.40 (same range of J is fine)
..............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a 2-level
Solver ?? either by running Solver manually or by a macro. And, is it
possible to do so relying entirely on the Solver internal trial solutions
without establishing the relation between P1s and E1s (which is not easy to
do) ??

Thank you kindly.

On Jan 29, 12:00 pm, monir wrote:
Hello;

I would very much appreciate your help in the following relatively simple
non-linear optimization problem.

1) By applying Solver:
....Set target cell: E18 to max....(E18 represents dependent variable E)
....By changing cells: J18 ..........(J18 represents independent variable J)
............................: P18...........(P18 represents independent
variable P)
....Subject to constraints: B18=1
....................................: J range 0.05 to 1.40
....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation of the
problem.
For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on the
changing combinations of J and P and settles for the combination that
produces max E in comparison with other combinations and subject to the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think, something like:
..Solver1: for each tried value of P, say, P1 in the range 0.50 to 1.50
..Set target cell: E18 to max.....(E18 represents dependent variable E)
..By changing cells: J18 ...........(J18 represents independent variable J)
..Subject to constraints: J range 0.05 to 1.40
.............(solution: J1, max E1 at each P1)
....Solver2:
....Set target cell: B18 = 1
....By changing cells: J18 ..........(J18 now represents the new variable J1)
....Subject to constraints: J1 range 0.05 to 1.40 (same range of J is fine)
.............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a 2-level
Solver ?? either by running Solver manually or by a macro. And, is it
possible to do so relying entirely on the Solver internal trial solutions
without establishing the relation between P1s and E1s (which is not easy to
do) ??

Thank you kindly.

monir,

Well first of all, what are you trying to maximize? If your 1 is
formulated properly then yes, it is doing what it is supposed to be
doing. So if not E then what?

It's better to describe your problem in equation (not Excel) form
because that is much clearer to another modeler. I.e., it would be
good to know what the functional relationship of P's and J's are to
E. BTW, if P and J are changing cells then they are decision
variables. It's not clear what they are either. Are they single
cells or a vector of cells. What are the non-linear constraint
functions that are functions of them? If you could post that it would
be helpful.

Your 2 implies E is only a function of J unless there is some sort of
plus/minus symmetry between J and P. But then you could make a P a
function of J and replace all the P's in your formulation. But
assuming there is not then P is not in the basis for any value of J.
Otherwise you'd a different Emax. So if P is not in the objective
function and always non basic. I don't see how including it can
contribute to the formulation.

You proposed parametric analysis in 3, reads to me like you'd get the
same Emax values when parameterizing P if I your previous statement
right:

For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.

So you lost me. You should not need two Phases to solve your problem,
because that implies that Phase II is a function of the Phase I
decision variables which implies that you can formulate all of the
decision variables in a single Phase because you know what their
functional relationships are. Splitting up the formulation, on its
face will provide no analytical value.

But if you want to optimize in two phases for whatever reason, yes
you'd write a VBA sub that calls solver and it's formulation twice,
saving Phase I outputs as inputs to Phase II. Frontline has a web
page with the VBA solver functions and return codes. You have to have
your maintenance paid up to access it though. The easiest way to
start your formulation is to use the macro recorder when completing
the solver dialog box. That will capture most of the code you need.
You may need to adjust the ranges in the formulation code to point to
both Phase I and Phase inputs/outputs based on what Phase is being
evaluated.

Good Luck,

SteveM

Steve;

Thank you for your prompt reply.

a) Let me first clarify item 2) of my OP.
The 2nd sentence should read:
"For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1, which DOES NOT necessarily satisfy the
constraint B18 = 1."

b) Please review my suggestion item 3) of my OP in light of the above
clarification.

c) I'm running Excel Solver from a macro.

d) FYI. Frontline Premium Solver is poorly developed and technically
unreliable! I've extensively tested it earlier on, and reported its numerous
and serious errors to Frontline.

Any thoughts ?? Thank you


"SteveM" wrote:

On Jan 29, 12:00 pm, monir wrote:
Hello;

I would very much appreciate your help in the following relatively simple
non-linear optimization problem.

1) By applying Solver:
....Set target cell: E18 to max....(E18 represents dependent variable E)
....By changing cells: J18 ..........(J18 represents independent variable J)
............................: P18...........(P18 represents independent
variable P)
....Subject to constraints: B18=1
....................................: J range 0.05 to 1.40
....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation of the
problem.
For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on the
changing combinations of J and P and settles for the combination that
produces max E in comparison with other combinations and subject to the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think, something like:
..Solver1: for each tried value of P, say, P1 in the range 0.50 to 1.50
..Set target cell: E18 to max.....(E18 represents dependent variable E)
..By changing cells: J18 ...........(J18 represents independent variable J)
..Subject to constraints: J range 0.05 to 1.40
.............(solution: J1, max E1 at each P1)
....Solver2:
....Set target cell: B18 = 1
....By changing cells: J18 ..........(J18 now represents the new variable J1)
....Subject to constraints: J1 range 0.05 to 1.40 (same range of J is fine)
.............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a 2-level
Solver ?? either by running Solver manually or by a macro. And, is it
possible to do so relying entirely on the Solver internal trial solutions
without establishing the relation between P1s and E1s (which is not easy to
do) ??

Thank you kindly.

monir,

Well first of all, what are you trying to maximize? If your 1 is
formulated properly then yes, it is doing what it is supposed to be
doing. So if not E then what?

It's better to describe your problem in equation (not Excel) form
because that is much clearer to another modeler. I.e., it would be
good to know what the functional relationship of P's and J's are to
E. BTW, if P and J are changing cells then they are decision
variables. It's not clear what they are either. Are they single
cells or a vector of cells. What are the non-linear constraint
functions that are functions of them? If you could post that it would
be helpful.

Your 2 implies E is only a function of J unless there is some sort of
plus/minus symmetry between J and P. But then you could make a P a
function of J and replace all the P's in your formulation. But
assuming there is not then P is not in the basis for any value of J.
Otherwise you'd a different Emax. So if P is not in the objective
function and always non basic. I don't see how including it can
contribute to the formulation.

You proposed parametric analysis in 3, reads to me like you'd get the
same Emax values when parameterizing P if I your previous statement
right:

For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.

So you lost me. You should not need two Phases to solve your problem,
because that implies that Phase II is a function of the Phase I
decision variables which implies that you can formulate all of the
decision variables in a single Phase because you know what their
functional relationships are. Splitting up the formulation, on its
face will provide no analytical value.

But if you want to optimize in two phases for whatever reason, yes
you'd write a VBA sub that calls solver and it's formulation twice,
saving Phase I outputs as inputs to Phase II. Frontline has a web
page with the VBA solver functions and return codes. You have to have
your maintenance paid up to access it though. The easiest way to
start your formulation is to use the macro recorder when completing
the solver dialog box. That will capture most of the code you need.
You may need to adjust the ranges in the formulation code to point to
both Phase I and Phase inputs/outputs based on what Phase is being
evaluated.

Good Luck,

SteveM

On Jan 29, 2:21 pm, monir wrote:
Steve;

Thank you for your prompt reply.

a) Let me first clarify item 2) of my OP.
The 2nd sentence should read:
"For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1, which DOES NOT necessarily satisfy the
constraint B18 = 1."

b) Please review my suggestion item 3) of my OP in light of the above
clarification.

c) I'm running Excel Solver from a macro.

d) FYI. Frontline Premium Solver is poorly developed and technically
unreliable! I've extensively tested it earlier on, and reported its numerous
and serious errors to Frontline.

Any thoughts ?? Thank you

"SteveM" wrote:
On Jan 29, 12:00 pm, monir wrote:
Hello;

I would very much appreciate your help in the following relatively simple
non-linear optimization problem.

1) By applying Solver:
....Set target cell: E18 to max....(E18 represents dependent variable E)
....By changing cells: J18 ..........(J18 represents independent variable J)
............................: P18...........(P18 represents independent
variable P)
....Subject to constraints: B18=1
....................................: J range 0.05 to 1.40
....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation of the
problem.
For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on the
changing combinations of J and P and settles for the combination that
produces max E in comparison with other combinations and subject to the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think, something like:
..Solver1: for each tried value of P, say, P1 in the range 0.50 to 1.50
..Set target cell: E18 to max.....(E18 represents dependent variable E)
..By changing cells: J18 ...........(J18 represents independent variable J)
..Subject to constraints: J range 0.05 to 1.40
.............(solution: J1, max E1 at each P1)
....Solver2:
....Set target cell: B18 = 1
....By changing cells: J18 ..........(J18 now represents the new variable J1)
....Subject to constraints: J1 range 0.05 to 1.40 (same range of J is fine)
.............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a 2-level
Solver ?? either by running Solver manually or by a macro. And, is it
possible to do so relying entirely on the Solver internal trial solutions
without establishing the relation between P1s and E1s (which is not easy to
do) ??

Thank you kindly.

monir,

Well first of all, what are you trying to maximize? If your 1 is
formulated properly then yes, it is doing what it is supposed to be
doing. So if not E then what?

It's better to describe your problem in equation (not Excel) form
because that is much clearer to another modeler. I.e., it would be
good to know what the functional relationship of P's and J's are to
E. BTW, if P and J are changing cells then they are decision
variables. It's not clear what they are either. Are they single
cells or a vector of cells. What are the non-linear constraint
functions that are functions of them? If you could post that it would
be helpful.

Your 2 implies E is only a function of J unless there is some sort of
plus/minus symmetry between J and P. But then you could make a P a
function of J and replace all the P's in your formulation. But
assuming there is not then P is not in the basis for any value of J.
Otherwise you'd a different Emax. So if P is not in the objective
function and always non basic. I don't see how including it can
contribute to the formulation.

You proposed parametric analysis in 3, reads to me like you'd get the
same Emax values when parameterizing P if I your previous statement
right:

For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.

So you lost me. You should not need two Phases to solve your problem,
because that implies that Phase II is a function of the Phase I
decision variables which implies that you can formulate all of the
decision variables in a single Phase because you know what their
functional relationships are. Splitting up the formulation, on its
face will provide no analytical value.

But if you want to optimize in two phases for whatever reason, yes
you'd write a VBA sub that calls solver and it's formulation twice,
saving Phase I outputs as inputs to Phase II. Frontline has a web
page with the VBA solver functions and return codes. You have to have
your maintenance paid up to access it though. The easiest way to
start your formulation is to use the macro recorder when completing
the solver dialog box. That will capture most of the code you need.
You may need to adjust the ranges in the formulation code to point to
both Phase I and Phase inputs/outputs based on what Phase is being
evaluated.

Good Luck,

SteveM

Not really. I still don't know what your problem is because I have
not seen the mathematical representation. You really can't be helped
without the "helper" knowing what that is.

There are some free NLP solvers out there. IPOPT is one. I have not
used it but you can find info he

http://www.coin-or.org/Ipopt/ipopt-fortran.html

And yes Frontline is a dog. It stinks with MIP problems too. But
it's MIP capabilities are less stinky that it's NLP capabilities.
That being said, if the problem hangs up the NLP solver, you may be
able to formulate it as a piece-wise MIP and use the linear solver.
But again, without knowing what the problem is, I don't know if that
is feasible either.

SteveM

Steve;

Thank you kindly for your thoughtful reply.

It's very clear that Excel / Solver (also developed by Frontline!) is the
problem!!! No surprise.

1) I've reformulated the optimization problem by deriving the analytical
expression for the 1st derivative d(E)/d(J) as a function of J and P. The
problem now reads:
Given the relations:
...T = fn1(J, P)
...Q = fn2(J, P)
...E = fn3(T, Q, J)
...B = fn4(J, Q)
find the values of:
...J (bet 0.05 and 1.40)
...P (bet 0.50 and 1.50)
such that:
...target: d(E)/d(J) = 0.0
...constraint: B = a known B0
(fn1, fn2, fn4 are complicated, lengthy, 6th deg polys, ~ 1 page long each.
That's why I didn't include them in my OP.)

2) Now the Solver solution (J, P, E) is perfect!!

3) I've never used or even heard of NLP IPOPT (Fortran)!! but will look at
it. Thanks for the link.

4) One of the difficulties would be to convert IPOPT to VBA. It is hard to
know without examining the code. My programming experience is about 60/40
Fortran/VBA. But again, the feasibility of the task would depend on the
complexity of IPOPT.
Based on your expertise in the field, do you know of or used a reliable VBA
optimizer macro ??

Thank you once again for your help.
Monir


"SteveM" wrote:

On Jan 29, 2:21 pm, monir wrote:
Steve;

Thank you for your prompt reply.

a) Let me first clarify item 2) of my OP.
The 2nd sentence should read:
"For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1, which DOES NOT necessarily satisfy the
constraint B18 = 1."

b) Please review my suggestion item 3) of my OP in light of the above
clarification.

c) I'm running Excel Solver from a macro.

d) FYI. Frontline Premium Solver is poorly developed and technically
unreliable! I've extensively tested it earlier on, and reported its numerous
and serious errors to Frontline.

Any thoughts ?? Thank you

"SteveM" wrote:
On Jan 29, 12:00 pm, monir wrote:
Hello;

I would very much appreciate your help in the following relatively simple
non-linear optimization problem.

1) By applying Solver:
....Set target cell: E18 to max....(E18 represents dependent variable E)
....By changing cells: J18 ..........(J18 represents independent variable J)
............................: P18...........(P18 represents independent
variable P)
....Subject to constraints: B18=1
....................................: J range 0.05 to 1.40
....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation of the
problem.
For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on the
changing combinations of J and P and settles for the combination that
produces max E in comparison with other combinations and subject to the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think, something like:
..Solver1: for each tried value of P, say, P1 in the range 0.50 to 1.50
..Set target cell: E18 to max.....(E18 represents dependent variable E)
..By changing cells: J18 ...........(J18 represents independent variable J)
..Subject to constraints: J range 0.05 to 1.40
.............(solution: J1, max E1 at each P1)
....Solver2:
....Set target cell: B18 = 1
....By changing cells: J18 ..........(J18 now represents the new variable J1)
....Subject to constraints: J1 range 0.05 to 1.40 (same range of J is fine)
.............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a 2-level
Solver ?? either by running Solver manually or by a macro. And, is it
possible to do so relying entirely on the Solver internal trial solutions
without establishing the relation between P1s and E1s (which is not easy to
do) ??

Thank you kindly.

monir,

Well first of all, what are you trying to maximize? If your 1 is
formulated properly then yes, it is doing what it is supposed to be
doing. So if not E then what?

It's better to describe your problem in equation (not Excel) form
because that is much clearer to another modeler. I.e., it would be
good to know what the functional relationship of P's and J's are to
E. BTW, if P and J are changing cells then they are decision
variables. It's not clear what they are either. Are they single
cells or a vector of cells. What are the non-linear constraint
functions that are functions of them? If you could post that it would
be helpful.

Your 2 implies E is only a function of J unless there is some sort of
plus/minus symmetry between J and P. But then you could make a P a
function of J and replace all the P's in your formulation. But
assuming there is not then P is not in the basis for any value of J.
Otherwise you'd a different Emax. So if P is not in the objective
function and always non basic. I don't see how including it can
contribute to the formulation.

You proposed parametric analysis in 3, reads to me like you'd get the
same Emax values when parameterizing P if I your previous statement
right:

For any value of the changing variable P, say P1, in the range P=0.50 to
1.50, there is a max E1 at J1.

So you lost me. You should not need two Phases to solve your problem,
because that implies that Phase II is a function of the Phase I
decision variables which implies that you can formulate all of the
decision variables in a single Phase because you know what their
functional relationships are. Splitting up the formulation, on its
face will provide no analytical value.

But if you want to optimize in two phases for whatever reason, yes
you'd write a VBA sub that calls solver and it's formulation twice,
saving Phase I outputs as inputs to Phase II. Frontline has a web
page with the VBA solver functions and return codes. You have to have
your maintenance paid up to access it though. The easiest way to
start your formulation is to use the macro recorder when completing
the solver dialog box. That will capture most of the code you need.
You may need to adjust the ranges in the formulation code to point to
both Phase I and Phase inputs/outputs based on what Phase is being
evaluated.

Good Luck,

SteveM

Not really. I still don't know what your problem is because I have
not seen the mathematical representation. You really can't be helped
without the "helper" knowing what that is.

There are some free NLP solvers out there. IPOPT is one. I have not
used it but you can find info he

http://www.coin-or.org/Ipopt/ipopt-fortran.html

And yes Frontline is a dog. It stinks with MIP problems too. But
it's MIP capabilities are less stinky that it's NLP capabilities.
That being said, if the problem hangs up the NLP solver, you may be
able to formulate it as a piece-wise MIP and use the linear solver.
But again, without knowing what the problem is, I don't know if that
is feasible either.

SteveM

1) I've reformulated the optimization problem by deriving the analytical
expression for the 1st derivative d(E)/d(J) as a function of J and P.

(fn1, fn2, fn4 are complicated, lengthy, 6th deg polys, ~ 1 page long each

Just a thought. Without knowing what you have, solving 6th Deg Polyn. can
be hard, and have multiple solutions.(Derivative =0's)
Solver was probably seeing multiple local Min & Max points on your curve.
As one curve was increasing, probably the other curves were decreasing,
etc... Once Solver gets confused, it easily gives up. Once Solver locks
onto an incorrect local minimum, Solver doesn't have enought logic to get
out of it, and give proper warnings.
Make sure you model was not using functions like IF(), as these cause
problems for Solver.

Mentioning 6th Deg Polys with Solver should send a red flag! It may be
possible to write a vba routine using the Newton Method. That can sometimes
help.
You may have a valid solution, but my experience would suggest caution with
the answer given.

--
Dana DeLouis



"monir" wrote in message
...
Steve;

Thank you kindly for your thoughtful reply.

It's very clear that Excel / Solver (also developed by Frontline!) is the
problem!!! No surprise.

1) I've reformulated the optimization problem by deriving the analytical
expression for the 1st derivative d(E)/d(J) as a function of J and P. The
problem now reads:
Given the relations:
..T = fn1(J, P)
..Q = fn2(J, P)
..E = fn3(T, Q, J)
..B = fn4(J, Q)
find the values of:
..J (bet 0.05 and 1.40)
..P (bet 0.50 and 1.50)
such that:
..target: d(E)/d(J) = 0.0
..constraint: B = a known B0
(fn1, fn2, fn4 are complicated, lengthy, 6th deg polys, ~ 1 page long
each.
That's why I didn't include them in my OP.)

2) Now the Solver solution (J, P, E) is perfect!!

3) I've never used or even heard of NLP IPOPT (Fortran)!! but will look at
it. Thanks for the link.

4) One of the difficulties would be to convert IPOPT to VBA. It is hard
to
know without examining the code. My programming experience is about 60/40
Fortran/VBA. But again, the feasibility of the task would depend on the
complexity of IPOPT.
Based on your expertise in the field, do you know of or used a reliable
VBA
optimizer macro ??

Thank you once again for your help.
Monir


"SteveM" wrote:

On Jan 29, 2:21 pm, monir wrote:
Steve;

Thank you for your prompt reply.

a) Let me first clarify item 2) of my OP.
The 2nd sentence should read:
"For any value of the changing variable P, say P1, in the range P=0.50
to
1.50, there is a max E1 at J1, which DOES NOT necessarily satisfy the
constraint B18 = 1."

b) Please review my suggestion item 3) of my OP in light of the above
clarification.

c) I'm running Excel Solver from a macro.

d) FYI. Frontline Premium Solver is poorly developed and technically
unreliable! I've extensively tested it earlier on, and reported its
numerous
and serious errors to Frontline.

Any thoughts ?? Thank you

"SteveM" wrote:
On Jan 29, 12:00 pm, monir wrote:
Hello;

I would very much appreciate your help in the following relatively
simple
non-linear optimization problem.

1) By applying Solver:
....Set target cell: E18 to max....(E18 represents dependent
variable E)
....By changing cells: J18 ..........(J18 represents independent
variable J)
............................: P18...........(P18 represents
independent
variable P)
....Subject to constraints: B18=1
....................................: J range 0.05 to 1.40
....................................: P range 0.50 to 1.50
it produces a feasible solution (J, P, E), which is not exactly the
"correct" one (differs by about 10% of what it should be)

2) The difficulty is directly associated with the above formulation
of the
problem.
For any value of the changing variable P, say P1, in the range
P=0.50 to
1.50, there is a max E1 at J1.
Solver in 1) above appears to seek the solution for max E based on
the
changing combinations of J and P and settles for the combination
that
produces max E in comparison with other combinations and subject to
the
constraints.
This is clearly not what I had in mind!!

3) The correct formulation of the problem should be, I think,
something like:
..Solver1: for each tried value of P, say, P1 in the range 0.50 to
1.50
..Set target cell: E18 to max.....(E18 represents dependent
variable E)
..By changing cells: J18 ...........(J18 represents independent
variable J)
..Subject to constraints: J range 0.05 to 1.40
.............(solution: J1, max E1 at each P1)
....Solver2:
....Set target cell: B18 = 1
....By changing cells: J18 ..........(J18 now represents the new
variable J1)
....Subject to constraints: J1 range 0.05 to 1.40 (same range of J
is fine)
.............(solution: J2, P2, max E2)

4) How would you intelligently combine Solver1 and Solver2 as a
2-level
Solver ?? either by running Solver manually or by a macro. And, is
it
possible to do so relying entirely on the Solver internal trial
solutions
without establishing the relation between P1s and E1s (which is not
easy to
do) ??

Thank you kindly.

monir,

Well first of all, what are you trying to maximize? If your 1 is
formulated properly then yes, it is doing what it is supposed to be
doing. So if not E then what?

It's better to describe your problem in equation (not Excel) form
because that is much clearer to another modeler. I.e., it would be
good to know what the functional relationship of P's and J's are to
E. BTW, if P and J are changing cells then they are decision
variables. It's not clear what they are either. Are they single
cells or a vector of cells. What are the non-linear constraint
functions that are functions of them? If you could post that it
would
be helpful.

Your 2 implies E is only a function of J unless there is some sort of
plus/minus symmetry between J and P. But then you could make a P a
function of J and replace all the P's in your formulation. But
assuming there is not then P is not in the basis for any value of J.
Otherwise you'd a different Emax. So if P is not in the objective
function and always non basic. I don't see how including it can
contribute to the formulation.

You proposed parametric analysis in 3, reads to me like you'd get the
same Emax values when parameterizing P if I your previous statement
right:

For any value of the changing variable P, say P1, in the range
P=0.50 to
1.50, there is a max E1 at J1.

So you lost me. You should not need two Phases to solve your
problem,
because that implies that Phase II is a function of the Phase I
decision variables which implies that you can formulate all of the
decision variables in a single Phase because you know what their
functional relationships are. Splitting up the formulation, on its
face will provide no analytical value.

But if you want to optimize in two phases for whatever reason, yes
you'd write a VBA sub that calls solver and it's formulation twice,
saving Phase I outputs as inputs to Phase II. Frontline has a web
page with the VBA solver functions and return codes. You have to
have
your maintenance paid up to access it though. The easiest way to
start your formulation is to use the macro recorder when completing
the solver dialog box. That will capture most of the code you need.
You may need to adjust the ranges in the formulation code to point to
both Phase I and Phase inputs/outputs based on what Phase is being
evaluated.

Good Luck,

SteveM

Not really. I still don't know what your problem is because I have
not seen the mathematical representation. You really can't be helped
without the "helper" knowing what that is.

There are some free NLP solvers out there. IPOPT is one. I have not
used it but you can find info he

http://www.coin-or.org/Ipopt/ipopt-fortran.html

And yes Frontline is a dog. It stinks with MIP problems too. But
it's MIP capabilities are less stinky that it's NLP capabilities.
That being said, if the problem hangs up the NLP solver, you may be
able to formulate it as a piece-wise MIP and use the linear solver.
But again, without knowing what the problem is, I don't know if that
is feasible either.

SteveM