Page 179 - Excel for Scientists and Engineers: Numerical Methods

156 EXCEL: NUMERICAL METHODS

Figure 8-11. Calculation of a root of a function by the Newton-Raphson method.
The formulas in row 6 were filled down until convergence was observed.
(folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet Wewton-Raphson Method')

The starting value, in this case 5, was entered in cell 84. The formulas in
cells C4, D4, E4, F4 and G4 are, respectively,
C4: =3*B4"3+2.5*B4"2-5*B4-11 (the function y)

D4: =B4+0.0000001 *B4 (increment x by a small amount Ax)

E4: =3*D4"3+2.5*D4"2-5*D4-11 (this is y + Ay)

F4: =( E4-C4)/( D4-B4) (m = Ad Ay)

G4: =( F4*B4-C4)/F4 (Xnew = (m Xold-Yold)/m)
Then the formula =G4 was entered in cell B6, so as to use the improved x
value as the starting value in the next row (row 5 was left empty for purposes of
illustration only). The formulas in C4:G4 were copied and pasted into the
corresponding cells in row 6. Finally, the formulas in cells B6:G6 were Filled
Down into succeeding rows until convergence was observed in column G or a
sufficiently small value ofy was obtained in column C.

Using Goal Seek ...
Excel provides a built-in way to find a real root of a function. The Goal
Seek.. . command in the Tools menu can be used to perform what is sometimes
called "backsolving"; that is, it varies x in order to make y reach a specified
value. Thus you can use Goal Seek ... to find a value of x that makes the value