non-linear curve fitting?

Ric

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to

Hi,

I'm wondering if anyone can recommend a good add-in for excel that will do
non-linear curve fitting. Ideally I'd like the curve fitting algorithm to be
able to be called as a function in a cell so that I can easily place the
solved parameters where I want them, or at least have the program be
flexible enough to allow me to tell it exactly where I want the output and
what output I want. That is I don't want to have to contend with tons of
information I don't need when all I really want is to be able to place in
specific cells the solution to the parameters of an equation that fit my
data and the R2 value for that fit.

If anyone has a recommendation, I'd appreciate it.

thanks,
Ric

Tom Ogilvy

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to

Linest will do non-linear curve fitting. Check out the help.

Regards,
Tom Ogilvy
MVP Excel

Ric <ric...@hotmail.com> wrote in message
news:OezIi48N$GA....@cppssbbsa02.microsoft.com...

Richard Price

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to

Tom

I thought it was in the Help for LINEST too, but I can't find it now
(in XL97), other than a reference to LOGEST for exponential curve
fitting. However if Ric follows the See Also to TREND he'll find a
method for polynomial fitting which works equally well for LINEST.

There's a much slicker way of doing this using array constants for the
powers, which just came up in m.p.e.charting and is demonstrated on
Bernard Liengme's site:

http://www.stfx.ca/people/bliengme/

Richard Price

In article <Oa4gDa9N$GA.176@cppssbbsa05>,

"Tom Ogilvy" <twog...@email.msn.com> wrote:
> Linest will do non-linear curve fitting. Check out the help.
>
> Regards,
> Tom Ogilvy
> MVP Excel
>
> Ric <ric...@hotmail.com> wrote in message
> news:OezIi48N$GA....@cppssbbsa02.microsoft.com...
> > Hi,
> >
> > I'm wondering if anyone can recommend a good add-in for excel that
will do

> > non-linear curve fitting...

Sent via Deja.com http://www.deja.com/
Before you buy.

Jerry W. Lewis

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to Ric

What types of non-linear models do you want to fit?

Polynomials
f = a +b*x +c*x^2 +d*x^3 +...
are a linear combination of the unknown parameters (a, b, c, d, ...) and
so can be fit by LINEST as other posters have already noted. Note that
the help does tell you how to do it, it just fails to explicitly mention
that the multiple x values could simply be powers of one x.

Exponentials
f = exp(a +b*x +c*x^2 +...
are are not linear combinations of unknown parameters, but can be
linearized as
LN(f) = a +b*x +c*x^2 +d*x^3 +...
which can be fit by LINEST

Products
f = a *b*x *c*x^2 *d*x^3 *...
can also be linearized (and then fit by LINEST) by taking logarithms
LN(f) = a +b+x +c+x*2 +d+x*3 +...
or the product model can be directly fit by LOGEST, which will do the
linearization for you.

... hopefully you get the idea.

If you need to fit a function that is neither linear nor linearizable in
the unknown parameters, create a cell formula that contains the sum,
over all observed values, of the squared deviations
(observed - f(parameters))^2
then use the Solver to minimize this cell by changing parameters.

R2 = SUM(suared fitted values) / SUM(squared observed values)

You could also have Solver maximize R2 to estimate the parameters. If
computers had infinite precision this would be equivalent to minimizing
the squred deviations. But with finite precision maximizing R2 would be
more numerically unstable.

Jerry W. Lewis
Stat...@erols.com

Ric wrote:
>
> Hi,
>
> I'm wondering if anyone can recommend a good add-in for excel that will do

John Stephens

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to

On Fri, 26 Nov 1999 00:21:19 -0500, "Ric" <ric...@hotmail.com> wrote:

>Hi,
>
>I'm wondering if anyone can recommend a good add-in for excel that will do
>non-linear curve fitting. Ideally I'd like the curve fitting algorithm to be
>able to be called as a function in a cell so that I can easily place the
>solved parameters where I want them, or at least have the program be
>flexible enough to allow me to tell it exactly where I want the output and
>what output I want. That is I don't want to have to contend with tons of
>information I don't need when all I really want is to be able to place in
>specific cells the solution to the parameters of an equation that fit my
>data and the R2 value for that fit.
>
>If anyone has a recommendation, I'd appreciate it.

Not an Excel addin, but here is the URL for a pretty good shareware curve
fitting program. Has many curve algorithms, and you can define your own
too.

http://www.ebicom.net/~dhyams/cvxpt.htm

--
John Stephens (remove spaces for legal email address)
s t e p h e n s @ p o b o x . c o m

John Stephens

unread,

Nov 26, 1999, 3:00:00 AM11/26/99

to

As I read it, LINEST does a least squares fit to a STRAIGHT LINE thru the
data. That is NOT non-linear curve fitting.

On Fri, 26 Nov 1999 01:22:51 -0500, "Tom Ogilvy" <twog...@email.msn.com>
wrote:

>Linest will do non-linear curve fitting. Check out the help.
>
>Regards,
>Tom Ogilvy
>MVP Excel
>
>
>Ric <ric...@hotmail.com> wrote in message
>news:OezIi48N$GA....@cppssbbsa02.microsoft.com...

>> Hi,
>>
>> I'm wondering if anyone can recommend a good add-in for excel that will do
>> non-linear curve fitting. Ideally I'd like the curve fitting algorithm to
>be
>> able to be called as a function in a cell so that I can easily place the
>> solved parameters where I want them, or at least have the program be
>> flexible enough to allow me to tell it exactly where I want the output and
>> what output I want. That is I don't want to have to contend with tons of
>> information I don't need when all I really want is to be able to place in
>> specific cells the solution to the parameters of an equation that fit my
>> data and the R2 value for that fit.
>>
>> If anyone has a recommendation, I'd appreciate it.
>>

>> thanks,
>> Ric

David J. Braden

unread,

Nov 27, 1999, 3:00:00 AM11/27/99

to John Stephens

John,
You read it wrong. The fit is to one that is linear in the coefficients. Fitting
the cubic
y = a + b*x + c*x^2 + d*x^3
is certainly within the purview of LINEST; the equation is linear in the
parameters to be estimated. Any transform that can get you linearity in the
coefficients allows you to use LINEST.

HTH
Dave Braden (I do stats)
TMY Research
Microsoft MVP - Excel, 1999

David J. Braden

unread,

Nov 27, 1999, 3:00:00 AM11/27/99

to Tom Ogilvy

Tom,
the "nonlinearity" is in y = f(x), not the coefficients of the (possibly)
transformed model.

Dave Braden
Yet another MVP Excel

unread,

Nov 28, 1999, 3:00:00 AM11/28/99

to

Many thanks for the information below. It's a pity that Microsoft doesn't
document the general nature of LINEST, when coupled with Excel arrays.

Took me a minute to figure out that the *{1,2} in example below should be a
^{1,2} . :-) [ but then I've been a bit slow on this topic :-) ]

Thanks again.

On Sat, 27 Nov 1999 23:05:48 -0500, "Tom Ogilvy" <twog...@email.msn.com>
wrote:

>John,

>This is the post from Charting that Richard Price wrote about in this
>thread. It is by Bernard Liengme. He has an example at his site.
>
>===<Dr. Liengme's post:>==
>Hello Bill,
>You need to get familiar with the LINEST equation. Yes, it
>will work for polynomials, notwithstanding its name. Select a
>range 5 rows by 3 column and enter
>=LINEST(your-y-value-range,
>your-x-value-range*{1,2},true,true) and complete the entry
>using shift+ctrl+enter. To get only the coeffiecinets with
>not statistics, select 3 cells in a row and use
>LINEST(your-y-value-range, your-x-value-range*{1,2}) and
>complete the entry using shift+ctrl+enter. See
>http://www.stfx.ca/people/bliengme for more details
>Best wishes
>Bernard
>
>==<End Post by Dr. Liengme>==
>
>Regards,
>Tom Ogilvy
>MVP Excel

--

Jerry W. Lewis

unread,

Nov 29, 1999, 3:00:00 AM11/29/99

to Ric

Try it with a linear model (any number of X columns) and compare results
to LINEST. Say your y-values are in A1:A10 and your predicted y-values
are in C1:C10, then
R2 = DEVSQ(C1:C10)/DEVSQ(A1:A10) = VAR(C1:C10)/VAR(A1:A10)
This would extend to any model linear or otherwise that has a fitted
intercept term.

Jerry

> > > I'm wondering if anyone can recommend a good add-in for excel that will
> do

Ric

unread,

Nov 29, 1999, 3:00:00 AM11/29/99

to

Sorry, what do you mean by fitted intercept term? I tried this on my models
(non-linear) and it did not give me the same result as reported by the curve
fitting programs I've playing with. In fact, on the 2 curves I tried it on
it gave me r2 values of >1, which isn't supposed to happen (as far as I
know)... Is there a more general definition of r2 that applies to any model?

I did try your suggestion of using solver to minimize the sum of square
deviation and that gave me the same answers (within 4 digits) of the other
programs I tried. In fact it was actually a fair bit faster in doing the
calculations. I think that would work for me but I still need the R2 to
evaluate the fit of different models to the data.

Thanks,
Ric

"Jerry W. Lewis" <stat...@erols.com> wrote in message

news:38420B30...@erols.com...

David J. Braden

unread,

Dec 3, 1999, 3:00:00 AM12/3/99

to Ric

Why use R2 as a measure of fit????

Evidently you take a frequentist approach, but consider, given your bias, other
measures of "closeness". Further, you can do much better with resampling and
avoid the implicit assumptions you make on "error" terms. As for a "close" fit,
consider the KS statistic.

Further, I would imbed your question into a decision-making one: If your
approaches don't lead to different decisions, why continue? If you have a hunch
that the "classical" approaches don't shed insight, then you have to go
Bayesian. IMO, it's the only way to go.

Regards,
Dave Braden (PhD)
Microsoft MVP - Excel 1999

Ric

unread,

Dec 4, 1999, 3:00:00 AM12/4/99

to

"David J. Braden" <t...@fiastl.net> wrote in message
news:38481E61...@fiastl.net...

> Why use R2 as a measure of fit????

Because I really don't know much about statistics and all the programs I've
used that fit regression lines or non-linear curves give the R2. So that's
what we use because it's there... To be honest, I don't entirely know what
the R2 is supposed to represent...

> Evidently you take a frequentist approach, but consider, given your bias,
other
> measures of "closeness". Further, you can do much better with resampling
and
> avoid the implicit assumptions you make on "error" terms. As for a
"close" fit,
> consider the KS statistic.

Sorry, you've lost me. Again, I don't know much about statistics so you'll
have to speak slowly and use small words... :) What exactly do you mean by
resampling, and briefly, how's it done? (perhaps you can link me to a
resource). And what is the KS statistic and how is it better than the R2?

>
> Further, I would imbed your question into a decision-making one: If your
> approaches don't lead to different decisions, why continue? If you have a
hunch
> that the "classical" approaches don't shed insight, then you have to go
> Bayesian. IMO, it's the only way to go.

Hmm, what is Bayesian? What I'm doing is fitting data from biochemical
experiments to the theoretical model that describes them (limited as it is)
to determine certain parameters like the affinity between 2 molecules. This
does lead to insight and that's really all I need to do is have a decent,
reasonably consistent way of determining these parameters. Conversion to
linear form to draw a linear regression tends to compound errors which
introduces significant artificats in the analysis. I've had much better luck
with non-linear regression. I've tried the method suggested here before of
using excel's solver to minimize the sum of square differences and it does
seem to work fine and give me answers that make sense. Does any of what you
mention above add more to it than that?

Thanks,
Ricardo

David J. Braden

unread,

Dec 5, 1999, 3:00:00 AM12/5/99

to Ric, Blank, Stan

Ricardo,
I'll try to summarize this thread. From this point on, you can contact me privately.

There are many ways to go about getting a "fit" to data. The state-of-the-art is
to incorporate prior information about the phenomena into the regressions, with
a clearly-defined objective that ideally incorporates decisions that have to be
made, and their consequences (this is a Bayesian approach).

The approach you seem to want to take was great up to about 1950, but is silly
now. BC (Before Computers), many made restrictive assumptions concerning the
error terms to develop an elegant set of mathematical approaches to dealing with
distributions on the estimates. These days, you can toss the assumptions, and
get far more insight, with less hassle, through resampling (bootstrapping, etc.)

Resampling is an idea that tosses aside distributional assumptions about the
"error" terms. It lets the data speak for itself. It uses Monte Carlo
simulation, with some adjustments, to fit the model and to gain insight into the
distribution of the estimators. It is easy to implement, fast to run, and a
hell of a lot better than pre-supposing distributions of error terms for
mathematical convenience. It has shown itself to be far superior to "classical"
techniques in many important cases, and never worse, and has led people to make
decisions they would not have made otherwise (all of which turned out good, by
the way).

Minimizing least squares is a holdover from the days when it was far easier to
use than the abs deviation, which is more robust. KS is Kolmogorov-Smirnov.

R2 (hopefully adjusted) is related to techniques that, IMHO, should have been
road-kill by now, but there are a lot of semi-trained statisticians who
promulgate old ideas, and seem unwilling to keep up with the remarkable
developments in the last 20 years.

To your very last query: Yes, yes, yes: Firstly, you should be able to
retransform the distributions you got by using LINEST. The direct fit with
SOLVER makes sense to me, though I would use ABS(dev) instead of dev^2. But to
get a handle on the distributions of your estimates, resampling/bootstrapping
(from your evident philosophic perspective) is the only way to go. For starters,
contact Stan Blank <sbl...@accessus.net>, who authors an intro-level package
that might be a good fit for you. For truly nonlinear regressions I use S-Plus
or my own code. And I *never* assume standard error-distributions. If I am to
do expert testimony in court, I take the approach espoused by Brad Effron, and
implemented by Stan, to "Let the data speak for itself". In a high-profile
business transaction, I incorporate prior assessments from major stakeholders in
the decision.

Again, for more help, send me e-mail.

Dave Braden
TMY Research