Page 128 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 105

Using LINEST as a Fitting Function
Instead of calculating a derivative at an x value corresponding to a table
entry, it may be necessary to obtain the derivative at an intermediate x value.
This problem is related to the process of interpolation, and indeed some of the
techniques from the preceding chapter can be applied here (see "Cubic
Interpolation" in Chapter 5). For example, we can obtain a piecewise fitting
function that applies to a localized region of the data set, and use the parameters
of the fitting function to calculate the derivative. In this section and the
following one, we will use a cubic equation
F(x) = ax3 + bx2 + cx +d (6- 13)
as the fitting function, using four data points to obtain the four coefficients of the
cubic. (The fitted curve will pass exactly through all four points and R2 will be
exactly 1 .) Once we have obtained the coefficients, the derivatives are calculated
from them; the first derivative is
F'(x) = 3ax2 + 2bx + c (6-14)
and the second derivative is
F"(x) = 6ax + 2b (6-15)
We can use the LINEST worksheet function (the multiple linear regression
worksheet function, described in detail in Chapter 13) to obtain the coefficients a,
b, c and d, then use the coefficients a, b, and c in equation 6-14 or 6-15 to
calculate the first or second derivatives.
The LINEST method will be illustrated using a table of absorbance data taken
at 5-nm increments, part of which is shown in Figures 6-6 and 6-7; the complete
range of x values is in $A$5:$A$85 and they values in $B$5:$B$85. We wish to
obtain the first derivative of this data set at 2-nm increments over the range 390-
415 nm.

Figure 6-6. Data used to calculate first and second derivatives.
(folder 'Chapter 06 Examples', workbook 'Derivs Using LINEST'. sheet 'Using megaformula')