Page 133 - Excel for Scientists and Engineers: Numerical Methods

110 EXCEL: NUMERICAL METHODS

First Derivative of a Worksheet Formula
Calculated by Using the Finite-Difference Method

The second example is a Function procedure that uses the finite-difference
method. The first derivative of a formula in a worksheet cell can be obtained with
a high degree of accuracy by evaluating the formula at x and at x + Ax. Since
Excel carries 15 significant figures, Ax can be made very small. Under these
conditions AyIAx approximates dyldx very well.
The user must "hard-code" the worksheet formula in VBA, in a suitable
form; the derivative is calculated by numerical differentiation. Again, the
function's only argument is the value of x, the independent variable. This
approach would be useful if the user is unable to provide an expression for the
derivative.

Function Deriv2(x)
OldY = fn(x)
xx = (1.00000001) * x
NewY = fn(xx)
Deriv2 = (NewY - OldY) / (xx - x)
End Function
Function fn(x)
'User codes the expression for the function here.
fn = 3 * x A 3 + 5 *x A 2 - 5 * x + 11
End Function
Figure 6-1 1. Function procedure to demonstrate calculation of first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part l)', module 'Modulel')

The Newton Quotient
In the previous section, the finite-difference method was shown to provide an
excellent estimate of the first derivative of a function expressed as a worksheet
formula. The multiplier used in the preceding user-defined function was
1.00000001. What is the optimum value of this multiplier, so that the Newton
quotient AylAx gives the best approximation to dyldx?
There are two sources of error in this finite-difference method of computing
dyldx: the approximation error, inherent in using a finite value of Ax, and the
roundoff error, due to the limited precision of the numbers stored in the
computer. We want to find the value of Ax that strikes the best balance between
these two errors. If hx is made too large, then the approximation error is large,
since dy/& -+ AyIAx only when Ax + 0. If Ax is made too small, then the
roundoff error is large, since we are obtaining Ay by subtracting two large and
nearly equal numbers, F(x) and F(x + Ax).