Here's an interesting problem, I wonder if anyone has any thoughts on
this. Recognize that my real problem is very complex (several
intermediate calculation including some iterative steps), but the
problem I'm having seems similar (conceptually anyway) to this simple
problem.

Given a data set:

x,y
10,3.9
8,3.2
7,2.8
6,2.2
5,1.4
4.5,0.8
4,0.01
3.8,-0.4
3.6,-1
3.5,-1.4
3.4,-1.8
3.3,-2.4
3.2,-3.2
3.1,-4.6
3.05,-6

One could look at the data and say, "that looks like the curve y=ln(x),
but with a different asymptote other than the y-axis and possibly a
scaling factor." So we choose a function of the form y=b*ln(x-a) to
correlate the data. So we add a third column =r1c5*ln(rc1-r1c6) where
r1c5 and r1c6 will hold our parameters b and a, then put
=sumxmy2(r2c3:r16c3,r2c2:r16c2) at the bottom of column 3. Then set up
solver to minimize r18c3 by changing r1c5:r1c6.

Now we pull initial guess for b and a out of a hat, and Solver runs
into an error. Because on the 2nd or 3rd iteration, solver is going to
try a value for a 3.05 and the LN function will return an error. We
try to improve the initial guesses, but, in this case, we would need to
be pretty close. I could get b=1.9, a=2.9 to converge, but b=1.8,a=2.8
wouldn't.

We iterate on each parameter individually, back and forth between b and
a, but this becomes tedious, especially if it takes several tries to
manually locate an initial a that will not generate an error.

For this simple model, one can add a constraint that a<=3.049999 and
thus avoid the error. However, in my real problem, the value for a
that generates an error isn't obvious. Also, it appears that the
optimum a value is essentially largest value that won't generate an
error. So I end up manually bisecting the interval between the lowest
value that generates an error and the highest value that doesn't until
I obtain the desired accuracy in a. Not the most efficient way to do
it, especially when I want to optimize b at the same time.

I don't know how much you'll be able to help, but it seems like an
interesting problem. I don't readily see an option that will tell
Solver to use those error values as part of the optimization algorithm,
even though the error values do contain useable information in this
case. All this exercise might do is show the importance of choosing
appropriate initial guesses for Solver, or that Solver isn't suitable
for solving all of the world's problems.

Any thoughts??


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=495283