Page 130 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 107

(vi) Use the coefficients a, b, and c to calculate the first or second derivative:
If these formulas are combined into one "megaformula", the result (entered in
cell E5 in Figure 6-8) is
=3*INDEX(LINEST(OFFSET(known~ys,MATCH(D5,x_values, 1)-2,0,4, I),
OFFSET(x-values, MATCH( D5,x_values, 1 )-2,0,4,1 )A{ 1,2,3}, 1 ,O)n 1 )*xA2
+2*INDEX(LINEST(OFFSET(known~ys,MATCH(D5,x~values, 1)-2,0,4, I),
OFFSET(x~values,MATCH(D5,x~values, 1)-2,0,4,1)A{1 ,2,3}, 1,0),2)*x
+INDEX(LINEST(OFFSET(known~ys,MATCH(D5,x~values,1)-2,0,4, I),
0 FFS ET( x-val ues, MATCH (D5, x-va I ues, 1 )-2,0,4,1 )A{ 1 ,2,3}, 1 , O), 3)
which is rather confusing. A better approach is to use named formulas. The
following table lists the named formulas and ranges used to calculate the first
derivative shown in Figure 6-7.
x-values =Sheet2!$A$5:$A$85
y-values =Sheet2!$8$5:$B$85
lookup-value =Sheet2!$D$5:$D$17
pointer =MATCH(INDIRECT(ROW()&":"&ROW()) lookup-value ,x-values, 1 )
known-xs =OFFSET(x~values,pointer-2,0,4,1)
known-ys =OFFSET(y-values,pointer-2,0,4,1)
LI N-array =LI N EST( Sheet2! known_ys,Sheet2! known-xsA{ 1 ,2,3}, 1 ,O)
aa =INDEX(LINEST-array,l)
bb =INDEX(LINEST_array,2)
cc =INDEX( LINEST_array,3)

Using these named formulas, the formula for the first derivative becomes
=3*aa*xA2+2*bb*x+cc

Note the formula used for pointer. It incorporates an "implicit intersection"
expression. Since both lookup-value and x-values are arrays, the formula
=MATCH(lookup-value ,x-values, 1)

returns an array of values instead of a single value. The formula using the
expression INDIRECT(ROW()&':"&ROW()) lookup-value returns a single value,
the value in the array lookup-value that is in the same row as the formula.