Page 336 - Excel for Scientists and Engineers: Numerical Methods

Chapter 14

Nonlinear Regression

Using the Solver

If you have read the preceding chapter on linear regression and are familiar
with the use of LINEST, you should have no trouble recognizing a function that is
linear in the coefficients. Some examples of functions that are linear in the
coefficients are y = a + bx + cx2 + dx3 or y = ae".
However, if the function is one such as

y=e a + bx (14-1)

it is not linear in the coefficients. It should be obvious that it's not possible to
apply LINEST to this equation; given a column of x values, you can't create a
column of e a + bx when a and b are the "unknowns" you're trying to find.
Some nonlinear equations can be transformed into a linear form. Equation
14-1, for example, can be transformed by taking the logarithm to the base e of
each side, to yield the equation
In y = a + bx ( 14-2)

which is linear in the coefficients.
Some equations cannot be converted into a linear form and are said to be
intrinsically nonlinear. Consider this example from the field of chemical reaction
kinetics: a system of two consecutive first-order reactions (the reaction scheme
A-B-C) where kl and k2 are the rate constants for the reaction of species A to
form the intermediate B and B to form the final product C, respectively. The
equations for the concentrations of the species [A],, [B], and [C], in a reaction
sequence of two consecutive first-order reactions can be found in almost any
kinetics text. The expression for [B], is

(1 4-3)

and a typical plot of [B], vs. t looks like the one in Figure 14-1. Equation 14-3 is
a classic example of an equation that is intrinsically nonlinear.

313