Page 134 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 6 DIFFERENTIATION 111

Excel carries 15 digits in its calculations, and it turns out that multiplying x
by a factor of 1.00000001 (a change in the 8th place) produces the minimum
error, before round-off error begins to have an effect. Figure 6- 12 illustrates this,
using a quadratic equation as an example; other functions give similar results.
The values in Figure 6-12 show that we can expect accuracy up to approximately
the tenth digit.

Figure 6-12. Newton quotient AyIAx as a function of the magnitude of Ax
(folder 'Chapter 06 Examples', workbook 'Derivs by VBA (Part l)', sheet 'Newton Quotient')

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

The spreadsheet shown in Figure 6-13 (see folder 'Chapter 06 Examples',
workbook 'Derivs by Sub Procedure') illustrates the calculation of the first
derivative of a function y = x3 - 3x2 - 130x + 150 by evaluating the function at x
and at x + Ax. Here a value of Ax of 1 x was used. For comparison, the first
derivative was calculated from the exact expression from differential calculus:
F(x) = 3x2 - 6x - 130.
The Excel formulas in cells Bll, C11, D11, Ell, F11, G11 and HI1
(columns C-F are hidden) are
B11 = t*xA3+u*xA2+v*x + w F(x)
C11 =All*(l+delta) X+Ax

D11 = t*C1 IA3+u*C1 IA2+v*C1 1 + w F(x + Ax)
El 1 =A1 l*delta Ax
F11 =D11-B11 AY

GI1 =FII/EII AJ?h
HI 1 =3*t*A1IA2+2*u*A1 1 +v dyldx from calculus