macro/function for Solver???

[bondcrash]

Hi all,

have the following problem - I have a cell which is dependent on the value of another - say A=f(B)

is there a function/macro so to see in real time:

what's the B value so that A=min?

I see there are some functions in the Solver Add-In called "solvok" etc but there is no help on how to use them.

Any suggestion????

Cheers

BC

[Leith Ross]

Hello Bond Crash,

It is better and faster to use GoalSeek for a single cell value. You find details of how to use it in Excel's Help.

Sincerely,
Leith Ross

[MrShorty]

Manually using Solver or Goal seek works well if this is something you would need to do once or occasionally. On the other hand, if you have a column full of cases, or if this is something you have to otherwise do multiple times, then it gets tedious to do it by hand, and it would be nice to automate the process.

Here's my story as it seems to apply to what you want to do.

I programmed in a user-defined function that I use quite frequently: ln(y)=A+B/x+Cln(x)+Dx^E. When given x, it is very straightforward to calculate y, because y is an explicit function of x. However, what if I'm given y? There's no way to manipulate this equation to get x as an explicit function of y, so I had to use a numerical method (as is used in the Solver subroutine) to solve for x as a function of y. When I first started putting this function together (I really wanted it to work as a function so it would update automatically like other worksheet functions), I tried to figure out how to call the solver subroutine from my function procedure and return the answer. Eventually I decided that it wasn't practical to call Solver from a Function procedure (if it's even possible), so I ended up writing my own code (using the Newton-Raphson algorithm) to solve for x as a function of y. After that, I quit trying to call solver from VBA.

I think to call Solver from VBA, you will have to call it from with a Sub procedure. Then you'll need to associate that sub procedure with an event (calculate event, for example) or make it volatile. When I searched VBA's online help, there was a decent example of how to use it under the SolverSolve function. I haven't tried it in practice myself to see how well it works, but it should provide a starting point.

Alternatively, if you know f(B) and it's a fairly normal (differentiable) function, it wouldn't be overly difficult to write your own minimization function that would work like a worksheet function.