Page 143 - Excel for Scientists and Engineers: Numerical Methods

120 EXCEL: NUMERICAL METHODS

Figure 6-20. Using the improved function procedure to calculate some first derivatives.
The optional argument scale-factor is used in row 9 to eliminate the #VALUE! error seen in row 8.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part 2)', sheet Better Function')

The examples in Table 6-20 illustrate the values of the first derivative
calculated by using the function dydx, compared with the "exact" values.
The worksheet formulas in column C and the corresponding functions in
column D are:
C4 =3*B4"3+5*B4"2-5*B4+11 D4 =dydx($C$4,$B$4)
C5 =SIN($B5) 05 =dydx(CS,B5)

C6 =EXP($B$6) D6 =dydx(CG,BG)
C7 =aAB7 D7 =dydx(C7,B7)
C8 =3*88"3+5*88"2-5*B8+1 1 D8 =dydx(C8,B8)
C9 =3*B9"3+5*B9"2-5*B9+11 D9 =dydx(C9,B9,1)

Rows 4-6 illustrate that relative, absolute or mixed references can be used in
the worksheet formula or in the arguments of the custom function. Row 9
illustrates the use of the optional argument scale-factor when the x value is zero.

Second Derivative of a Worksheet Formula
The VBA code for the Function procedure shown in Figure 6-21 requires
only slight modification to provide a function that returns the second derivative
of a function as a cell formula. The syntax of the d2xdy2 function is identical to
that of the function dydx.
The code is shown in Figure 6-21. The function calculates the central
derivative uing three points (see the formula in Table 6-1). Note that the
multiplier used to calculate Ax is 1E-4 instead of 1E-8.