Page 137 - Excel for Scientists and Engineers: Numerical Methods
P. 137
114 EXCEL: NUMERICAL METHODS
Figure 6-15. Calculating the first derivative of a formula.
(folder 'Chapter 06 Examples', workbook 'Derivs by Sub Procedure', sheet 'Deriv')
The Sub procedure saves the values of x and y from the worksheet (OldX and
OldY), then writes the incremented value of x (NewX) to the worksheet cell. This
causes the worksheet to recalculate and display the corresponding value of y + Ay
(NewY). The derivative is calculated and written to the destination cell. Finally,
the original value of x is restored. Figure 6-15 illustrates the spreadsheet of
Figure 6-13 after the Sub procedure has been run. The errors produced by this
method are much smaller than those produced by the function based on LINEST.
The code in Figure 6-14 can easily be modified to calculate the partial
derivatives of a function with respect to one or several parameters of the function
(e.g., dy/da, dy/db, etc.) for a cubic equation. Similar code is used in the SolvStat
macro (see Chapter 14, "The Solver Statistics Add-In") and a similar approach is
used in the Solver itself (see "How the Solver Works" in Chapter 14).
