Page 202 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 8 ROOTS OF EQUATIONS 179

600 -

500 -

400 -

300 -

200 '
100

I
-30 -20 -10 0 10 20 30

Figure 8-37. Two intersections of a straight line and a curve, calculated by using the
Newton-Raphson method with intentional circular references.
(folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference (2)')
It is merely necessary to use two identical Newton-Raphson formulas and
provide two different start values that will result in convergence to the two
different "roots." Figure 8-38 illustrates the set-up of the table. Cells C66 and
C67 contain the formula
=$1$5
(pointing to the cell that contains a constant). Guided by Figure 8-37, initial x
values of 10 and -10 were chosen. Figure 8-38 shows the cell values before the
intentional circular references have been created.

Figure 8-38. Calculating two intersections of a line and a curve
by the Newton-Raphson method (before creating intentional circular references).
(folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference (2)')
Once the formulas have been entered, replace the initial x values in cells A66
and A67 by the formulas =G66 and =G67, respectively, to create the two circular
references. The "Cannot resolve circular references" message will be displayed