SolverOK Function without Dialog Box

[Jason Carlos]

Hey,

I want to use solver by using the =solverok formula and not the dialog box so that the answer updates automatically in my spreadsheet. I have attached an example:


1) I want to change cell B6
2) To the Exact Number in B7
3) by Changing cell B8

Here is the solver formula I found online: =SolverOk (SetCell, MaxMinVal, ValueOf, ByChange,Engine, EngineDesc)

Here is what I have entered: =solverok(B8,3,B7,B6)

I have the solver add-in installed.

Thank you for the help!

[shg]

You can't use SolverOK as a user-defined function. You can automate Solver from VBA, and there are many examples in the forum.

But you probably don't need Solver for whatever it is you're trying to do, though I can't tell what that is.

[MrShorty]

The solverok() function cannot be called from a spreadsheet cell like that. Solver's routines need to be able to modify the values of the by changing cells, and that is something that a function in a cell cannot do.

I don't know my way around Excel's financial functions, but it seems that many of them are inter-related "inverse" functions. Like shg, I doubt Solver is needed for this problem, once you understand how the different financial functions are inter-related.

The help file for the PMT() (http://office.microsoft.com/en-us/ma...927.aspx?CTT=1 ) function suggests that PMT, FV, PV, RATE, are all inter-related. Since your spreadsheet appears to be trying to find the Rate, I expect you may want to research the RATE() function (http://office.microsoft.com/en-us/ma...in=HA102927927 ) to see if it will solve for the Rate at fixed values for the other parameters. Even if the rate() function is not correct, I expect someone who understands these financial functions would be able to recommend the correct "inverse" function for this problem.

If you decide it must be solved using Solver, automating Solver like this usually requires VBA sub procedure associated with the calculate event.

If nothing else, this will bump your post back to the top and get some additional exposure.