## Y m yi JVi

Central difference y, --~2

Backward difference }', , 2

Second derivative, using four points:

Forward difference y, --~2

Second derivative, using five points:

Third derivative, using four points

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

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

and the second derivative is

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 the y values in $B$5:$B$85. We wish to obtain the first derivative of this data set at 2-nm increments over the range 390415 nm. _ ______________________

A |
B | |

3 |
Otiyinal Dilta | |

4 |
Wavelength |
Absorbance |

23 |
390 |
0.552 |

24 |
395 |
0.582 |

25 |
400 |
0.598 |

26 |
405 |
0.600 |

27 |
410 |
0.586 |

28 |
415 |
0.559 |

29 |
420 |
0.521 |

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

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

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')

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.

(ii) Use the OFFSET function to select the four bracketing x values:

(iii)Use a similar formula to obtain the four bracketing y values:

(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(D6,known_xs,1)-2,0,4,1),

OFFSET(known_xs,MATCH(D6,known_xs,1)-2,0,4,1)A{1,2,3},1,0)

(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(LINEST_array,1)

(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,1 ), OFFSET(x_values,MATCH(D5,x_values, 1 )-2,0,4,1 )A{1,2,3}, 1,0), 1 )*xA2 +2*INDEX(LINEST(OFFSET(known_ys,MATCH(D5,x_values, 1 )-2,0,4,1 ), 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,1), OFFSET(x_values,MATCH(D5,x_values,1)-2,0,4,1)A{1,2,3},1,0),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!$B$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)

LIN_array =LINEST(Sheet2!known_ys,Sheet2!known_xsA{1,2,3},1,0)

aa =INDEX(LINEST_array,1)

bb =INDEX(LINEST_array,2)

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.

D |
E |
g m |
o I | |

4 |
X |
y |
m |
F"(x)x 10 |

5 |
39G |
0.552 |
0.00710 |
-4.53E-03 |

6 |
392 |
0.00616 |
-4.87E-03 | |

7 |
394 |
0.00516 |
-5.2ÜE-03 | |

8 |
396 |
0.00405 |
-5.4GE-D3 | |

9 |
398 |
0.00294 |
-5.65E-03 | |

10 |
400 |
0.598 |
0.00176 |
-5.84E-03 |

11 |
402 |
0 00059 |
-5 85E-03 | |

12 |
404 |
-0.00058 |
-5.87E-03 | |

13 |
406 |
-0.00179 |
-5 80E-03 | |

14 |
408 |
-0 00293 |
-5.65E-03 | |

15 |
410 |
0.586 |
-0 00408 |
-5 49E-03 |

16 |
412 |
-0,00515 |
-5.18E-03 | |

17 |
414 |
-0.00615 |
-4.88E-03 |

Figure 6-8. First derivative calculated using LI NEST function. The y values indicate the known experimental points, (folder 'Chapter 06 Examples', workbook 'Derivs Using LINEST', sheet 'Using named formulas')

0.61

0.60

0.59

3 LL

0.57

0.56

0.55

Figure 6-8. First derivative calculated using LI NEST function. The y values indicate the known experimental points, (folder 'Chapter 06 Examples', workbook 'Derivs Using LINEST', sheet 'Using named formulas')

0.61

0.60

0.59

0.56

0.55

0.010

0.005

0.010

0.005

400 405

Figure 6-9. Chart of values of first and second derivative calculated using LINEST. (folder 'Chapter 06 Examples', workbook 'Derivs Using LINEST', sheet 'Using named formulas')

Part of the table of calculated first derivative values is shown in Figure 6-8, and the values are charted in Figure 6-9. The formula used in cell F5, for example, is

One could use the x value where F(x) = 0 to locate the maximum in the spectrum.

Depending on the data table being differentiated, the errors in the values returned by this method may be as great as several percent.

## Post a comment