The preceding technique can be easily extended to find multiple intersections of two curves. The following figure illustrates how to find the two intersections of a horizontal straight line with a parabola, but many other types of curve can be handled.

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)')

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

(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.

A B |
C D |
E |
F G | ||

65 |
Table set-up before establishing circular references x y1 y2 x+Ax y1+Ay slope new x | ||||

66 |
10,000 150.0 |
300.0 |
10,00 |
150 |
27.00: 15.5556 |

67 |
-10.000 210,0 |
300.0 |
-10.00 |
210 |
-33.00 -12 727 |

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)')

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 and the two cells will display zero values. Now choose Options... from the Tools menu and choose the Calculation tab. Check the Iteration box and press OK. Figure 8-39 shows the final values in the table, after circular reference iteration is complete.

A |
B |
C D I Ë F |
G | |

65 |
Table after establishing circular references x v1 V2 x+Ax y1+Ay slope new x | |||

66 |
14 454 |
300.0 |
300.0 14.45 300: 40,36 |
14 4536 |

67 |
-12.454 |
300.0 |
300.0 -12.45 300 -40.36 |
-12.454 |

Figure 8-39. Calculating two intersections of a line and a curve by the Newton-Raphson method (after creating intentional circular references), (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference (2)')

Figure 8-39. Calculating two intersections of a line and a curve by the Newton-Raphson method (after creating intentional circular references), (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference (2)')

Was this article helpful?

## Post a comment