Page 310 - Excel for Scientists and Engineers: Numerical Methods

Chapter 13

Linear Regression

and Curve Fitting

"Curve fitting" is frequently used in scientific or engineering applications to
obtain the coefficients of a mathematical model that describes experimental data.
In Chapter 5 we saw how to obtain the equation of a curve that passes exactly
through a set of data points. This is the process of interpolation and requires (for
example) four coefficients to describe a curve that passes through four data
points. But what if, instead of four data points, we have 4000 data points? It
would be ludicrous to try to find the 4000-parameter equation that describes the
curve that passes through all the data points. Instead, we would like to find a
relatively simple mathematical relationship that does not necessarily pass through
data points but is a good fit to the data set as a whole. The "best fit" of a curve to
a set of data points is considered to be found when the sum of squares of the
deviations of the experimental points from the calculated curve is a minimum.
This procedure is known as least-squares curve fitting or, more generally, as
regression analysis. Excel provides several ways to obtain regression
coefficients; these are described in the following sections.

Linear Regression

Linear regression is not limited to the case of finding the least-squares slope
and intercept of a straight line. Linear regression methods can be applied to any
function that is linear in the coeficients'. Many functions that produce curved x-
y plots are linear in the coefficients, including power series, for example,

y = a + bx + cx2 + dx3 (13-1)

and some functions containing exponentials, such as

+ Mathematically, a function that is linear in the coefficients is one for which the partial
derivatives of the function with respect to the coefficients do not contain coeficients. For
example, for the power series equation y = a + bx + cx2, 8yIaa = 1, +lab = x and ayldc =
X2.

287