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