Page 132 - Excel for Scientists and Engineers: Numerical Methods

109
CHAPTER 6 DIFFERENTIATION --

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

=3*aa*xA2+2*bb*x+cc
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.

Derivatives of a Worksheet Formula
Instead of calculating the first or second derivative of a curve represented by
data points, we may wish to find the derivative of a function (a worksheet
formula). In the following, two different methods are illustrated to calculate the
first or second derivative of a worksheet formula by using a user-defined
function. The calculation of the first derivative of the function y = 3x3 + 5x2 - 5x
+ 11 is used as the example for each method

Derivatives of a Worksheet Formula
Calculated by Using a VBA Function Procedure
The first example is a Function procedure that returns the first derivative of a
specific worksheet formula. The expression for the derivative is "hard-coded" in
the VBA procedure. The user must be able to provide the expression for the
derivative and must modify the VBA code to apply it to a different formula. The
function's only argument is the value of x, the independent variable for which the
derivative is to be calculated. The main advantage of this approach is that the
returned value of the derivative is exact. This approach will execute the fastest
and would be suitable if the same formula is to be used many times in a
worksheet.

Function Derivl (x)
'User codes the expression for the derivative here.
Derivl =9*xA2+10*x-5
End Function
Figure 6-10. Function procedure to demonstrate calculation of a first derivative.
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part l)', module 'Modulel')