Automatic Solver

[curiouslearn]

(1) Suppose cell C1 is a complicated formula (objective function)
which depends on B1 (a parameter) and A1 (a variable)

(2) For every value of B1 suppose there is an A1 which maximizes C1.
Is there a way that whenever I change B1, the value of A1
automatically changes so that C1 is maximized.

I know how to find any new A1 corresponding to a new B1 by using
solver. But I have to go and do this manually.

I really appreciate any help.

 

[merjet]

Record a macro that runs Solver solving A1.

Then use the Worksheet_Change event to call the macro whenever B1 is
changed.

Hth,
Merjet

 

[TallBill]

Solver normally requires a final value for it to solve for. Since this
is not quite what you are wanting, then you will have to write a
routine to do this search. I created a workbook, just for fun, that
employs the "Golden Section" search method that I remember from
college many years ago. The code is a little over 100 lines, so is a
little big to post here, but I will send it to you for you to look
over. I included some extra "goodies" also: a separate worksheet to
"log" each value of the iteration, and a chart sheet to plot the
points.

You need to give the routine an upper and a lower limit for the values
in cell A1, so I put those in cells A2 and A3. It uses a button to
start the macro, rather than automatically calling the routine from
the worksheet Change event, but you could add this call very easily.
This allows one to enter various values in cell A1 to verify the
formula in cell C1, then push the button to find the value of A1 that
will maximize the value of C1. I used a parabola for the function in
cell C1. Just change cell C1 to your function.