EXCEL 97 Two-Variable: Nonlinear Regression

EXCEL 97: Nonlinear Regression

A. Colin Cameron, Dept. of Economics, Univ. of Calif. - Davis

This September 1999 help sheet gives information on how to

fit some nonlinear regression in general
fit some specific nonlinear trendlines to an XY scatterplot
fit a general curve to an XY scatterplot

STANDARD NONLINEAR REGRESSION MODELS

Regular regression can handle nonlinearity of the nonlinearity is in the regressors but not the parameters.
For example y = a + b*x^2 + u is okay, but y = a^b*x + u are not okay.

Leading estimable models are:

Linear-log: y = a + b*ln(x) + u
Log-linear: ln(y) = a + b*x + u
Log-log: ln(y) = a + b*ln(x) + u
Polynomial: y = a + b*x + c*x^2 + d*x^3 + .... + u

These can be implemented by using Tools | Data Analysis | Regression in the usual way.
Just transform the data e.g. from levels to natural logarithms.
These can be easily extended to more than one regressor.

The tricky parts are

interpretation of the coefficients: see a textbook
do not compare R-squared from models with different dependent variable.

SPECIFIC TRENDLINES FOR A SCATTERPLOT

For the case where there is just one regressor (x) one can add a nonlinear trendline to a scatter plot.

The method is choose Chart Wizard and XY Scatter Plot, and create an XY scatter plot.
Once the scatterplot is created right-click on the trendline (which will be a straight line) and choose one of the options:

Logarithmic: y = c + b*ln(x) + u
This is the linear-log relationship.
Exponential: y = c*exp(bx)*u
This is the log-linear relationship since taking logs gives ln(y) = ln(a) + b*ln(x) + ln(u)
Power: y = a*(x^b)*u
This is the log-log relationship since taking logs gives ln(y) = ln(a) + b*ln(x) + ln(u)
Polynomial: y = a + b*x + c*x^2 + d*x^4 + .... + u
Moving average.
This is generally only useful for time series data.

GENERAL TRENDLINES FOR A SCATTERPLOT

Methods of nonparametric regression permit fitting of general flexible curves to an XY scatter plot, without being restricted to pre-specified shapes such linear, exponential or power.

These methods include LOWESS, kernel regression, nearest-neighbors regression, local polynomial regression, splines, ...

For this you need a statistical or econometric package, though not all do this.
The statistical package S-Plus is the leader in this area.

For further information on how to use Excel go to
http://www.econ.ucdavis.edu/faculty/cameron