Page 129 - Excel for Scientists and Engineers: Numerical Methods

106 EXCEL: NUMERICAL METHODS

o.610 r Original data points

0.550 I 1 -
390 395 400 405 410 415 420
Wavelength, nm

Figure 6-7. Chart of some data used to calculate first and second derivatives.
(folder 'Chapter 06 Examples', workbook 'Derivs Using LINEST', sheet 'Using megaformula')
The steps required in the calculation of the first or second derivative at a
specified value of x are as follows:
(i) Use the MATCH function to find the position of the lookup value x in the
table of x values. The lookup value is in cell D5 in Figure 6-8.

=MATCH(D5, $A$5:$A$85,1)
(ii) Use the OFFSET function to select the four bracketing x values:
=OFFSET($A$S:$A$85,D5-2,0,4,1)
(iii)Use a similar formula to obtain the four bracketingy values:
=oFFSET($B$5:$B$85,D5-2,0,4,1)

(iv) Use these two arrays in the LINEST formula, raising the range of x values to
an array of powers; the LINEST formula must be entered in a horizontal
range of three cells, and you must press CONTROL+SHIFT+ENTER:
=LINEST(OFFSET(known-ys,MATCH(DG, known-xs, 1 )-2,0,4,1),
,
0 F FS ET( known-xs, MATCH ( D6, known-xs, 1 )-2,0,4,1 1 ,2,3}, 0)
1
)A{
(v) Use the INDEX function to obtain each of the regression coefficients a, b and
c from the LINEST array. (To simplify the formula, the cells containing the
preceding LINEST formula have been given the name LINEST-array.) The
following equation returns the coefficient a:

=INDEX( LI NEST-array ,I )