nonlinear regression/ curve fits

[geocalc]

I am attempting to fit a nonlinear curve to the data listed below using Excel
2003 with the standard stat add-in provided with Excel. I normally use SAS
for statistical analysis and am not familiar with all the capabilities of
Solver, etc. I would like to achieve the above curve fit solution using
Excel, since this is one step in an application to be used by others who do
not have SAS, but have basic Excel capability. In SAS I fit a nonlinear
regression curve of the form: lnY1 = b * exp( -a*lnX1), this provided
coefficients of b=-1.91 and a=0.515, with an R-square of 0.98 (not perfect
but close enough !). I have not been able to duplicate this in Excel. The
closest I can come (without expensive 3rd-party add in software), is to fit a
logarithmic curve (chart,trendline, etc) and this doesn't handle the limit
approached on the Y-axis very well. Any ideas ? (heres the data in its
original, non-Ln transformed format):
X Y
0.09 0.00123
0.15 0.0067
0.2 0.0137
0.4 0.0494
0.6 0.0852
0.8 0.1176
1 0.1465
1.2 0.1724
1.4 0.1957
1.6 0.2168
1.8 0.2361
2 0.2539
5 0.4193
10 0.5437
20 0.6544
40 0.7453
60 0.7888
80 0.8156
100 0.8343
500 0.9748
700 0.9787
1000 0.9822

 

[Gary''s Student]

You need Solver:

In Column A put your x-values.
In Column B put your y-values
In Column C put ln(y) values =LN(B1) and copy down.
In D1 enter the model equation =$F$2*EXP(-$F$1*LN(A1)) and copy down
In E1 enter error squared =(D1-C1)^2 and copy down
In E23 sum the square errors =SUM(E1:E22)
In F1 and F2 enter 1 (starter values for a and b)
You should see:
0.090 0.001 -6.701 11.111 317.262 1
0.150 0.007 -5.006 6.667 136.243 1
0.200 0.014 -4.290 5.000 86.311
0.400 0.049 -3.008 2.500 30.336
0.600 0.085 -2.463 1.667 17.052
0.800 0.118 -2.140 1.250 11.495
1.000 0.147 -1.921 1.000 8.531
1.200 0.172 -1.758 0.833 6.715
1.400 0.196 -1.631 0.714 5.501
1.600 0.217 -1.529 0.625 4.639
1.800 0.236 -1.443 0.556 3.996
2.000 0.254 -1.371 0.500 3.500
5.000 0.419 -0.869 0.200 1.143
10.000 0.544 -0.609 0.100 0.503
20.000 0.654 -0.424 0.050 0.225
40.000 0.745 -0.294 0.025 0.102
60.000 0.789 -0.237 0.017 0.064
80.000 0.816 -0.204 0.013 0.047
100.000 0.834 -0.181 0.010 0.037
500.000 0.975 -0.026 0.002 0.001
700.000 0.979 -0.022 0.001 0.001
1000.000 0.982 -0.018 0.001 0.000
633.703
Then run solver to minimize E23 by adjusting F1 and F2 and you should see:

0.090 0.001 -6.701 -6.600 0.010 0.515001356
0.150 0.007 -5.006 -5.074 0.005 -1.909895412
0.200 0.014 -4.290 -4.375 0.007
0.400 0.049 -3.008 -3.062 0.003
0.600 0.085 -2.463 -2.485 0.000
0.800 0.118 -2.140 -2.142 0.000
1.000 0.147 -1.921 -1.910 0.000
1.200 0.172 -1.758 -1.739 0.000
1.400 0.196 -1.631 -1.606 0.001
1.600 0.217 -1.529 -1.499 0.001
1.800 0.236 -1.443 -1.411 0.001
2.000 0.254 -1.371 -1.337 0.001
5.000 0.419 -0.869 -0.834 0.001
10.000 0.544 -0.609 -0.583 0.001
20.000 0.654 -0.424 -0.408 0.000
40.000 0.745 -0.294 -0.286 0.000
60.000 0.789 -0.237 -0.232 0.000
80.000 0.816 -0.204 -0.200 0.000
100.000 0.834 -0.181 -0.178 0.000
500.000 0.975 -0.026 -0.078 0.003
700.000 0.979 -0.022 -0.065 0.002
1000.000 0.982 -0.018 -0.054 0.001
0.038
With F1 and F2 having your desired values

 

[Bernard Liengme]

Using Solver I get b -1.90989
a 0.515003

did not bother with R2
If interested in how to use Solver send message to my private email
best wishes--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

 

[geocalc]

Thank you -- this works well ! I don't want to overly impose, but it would be
nice
to have an R-square associated with the fit -- and ultimately to plot the
data in a chart. I made a few quick attempts but no luck so far.

 

[Bernard Liengme]

Find SSresidual using SUMXMY2; SUMXMY2(Cvalues,Dvalues) using Gary's
solution
Find SStotal using DEVSQ: DEVSQ(Cvalues)
compute SSregression from SStotal = SSregression + SSresidual
compute R2 using R2= SSregression/SStotal; I get 0.9994
Hope J Lewis is around to double check this!
best wishes