Page 184 - Excel for Scientists and Engineers: Numerical Methods
P. 184
CHAPTER 8 ROOTS OF EOUATIONS 161
Figure 8-20. Using the secant method to obtain a root of a function.
(folder 'Chapter 08 Examples', workbook 'Roots of Equations', worksheet 'Secant Method')
The formulas in row 3 are identical to those in Figure 8-10, except that cell
C3 contains a value rather than a formula.
The Newton-Raphson Method
Using Circular Reference and Iteration
The Newton-Raphson method discussed in a previous section requires the
user to fill down formulas until convergence is observed visually. One can create
a Newton-Raphson calculation that runs automatically by using an intentional
circular reference.
A circular reference is created when a formula refers to itself, either directly
or indirectly. If a circular reference occurs, Excel issues a "Cannot resolve
circular references" message and displays a zero value in the cell. Usually,
circular references occur because the user entered an incorrect cell reference in
an equation. But occasionally a problem can be solved by intentionally creating
a circular reference.
The calculation is illustrated in Figure 8-21. A single change was made to
the worksheet in Figure 8- 11. After entering the formulas in row 4, the initial
value 5 in cell 84 was replaced by the formula =G4. In this way the improved
estimate of x was entered as the start value of the process.
