Hi,

Yes, when I analyzed your data starting with initial guess values of 'a'=2.8
and 'b'=1.8, parameter 'a' becomes 3.08 in the second iteration step (which
is slightly greater than the smallest value in the x-range, i.e., 3.05), and
the Solver stops and returns an error (due to the attempted calculation of
the logarithm of a negative value).

However, I could get around this problem with a slight modification as
follows;

I placed your x- and y- data in A2:A16, and B2:B16 respectively. I created
a dummy parameter (let's call it 'a prime') in D2, and the parameters 'a' and
'b' in E2 and F2 respectively.
In E2 (corresponding to 'a') I entered the formula,
=MIN(A2:A16)*0.999999-D2. I placed initial guess values, 1 for aprime (i.e.,
D2), and 1 for b (in F2).
In C2, I entered the formula =$F$2*LN(A2-$E$2), and autofilled the formula
down to C16; and placed the SSR in G2 with the formula
=SUMXMY2(B2:B16,C2:C16). The SSR was about 94.2 at this point.

I invoked the Solver, to minimize the SSR (G2), by changing 'aprime' and 'b'
(i.e., $D$2, $F$2), and under "Options" checked "Assume Non-Negative".
Solver didn't have any problem and returned the optimized values,
'aprime'=0.050096784 (corresponding to 'a'=2.999900166) and b=1.99943891, and
the minimized SSR was 0.005916092.

Now comes the interesting part: I tried the guess values, 'aprime' = -100
and 'b' = 100. This corresponds to 'a' = slightly less than 103.05, and it
returns error (#NUM) to the entire range C2:C16, vis-a-vis to SSR.
Surprisingly, Solver handled even this situation and returned the same
optimized values as before (it needed a little more than the default 100
iterations; of course I had turned off "Show Iteration Results" for this)!

I think, the bottom-line is, it is safer to use a dummy parameter to
incorporate a constraint to the actual parameter, and optimize the dummy
parameter (with the added constraint to disallow negative values for
'aprime', i.e., 'a' will never become greater than any x-value).

Regards,
B. R. Ramachandran


"MrShorty" wrote:


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