Page 201 - Excel for Scientists and Engineers: Numerical Methods

178 EXCEL: NUMERICAL METHODS

Figure 8-36. Using the Newton-Raphson method to find the intersection of two lines.
(folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')
Figure 8-36 shows the cells where the Newton-Raphson calculation is
performed, using an intentional circular reference (refer to the section "The
Newton-Raphson Method Using Circular Reference and Iteration" earlier in this
chapter if the method of calculation is not apparent). The formula in cell G38 is
=(C38+F38*A38-B38)/F38
The advantage of using the Newton-Raphson method with circular
references, compared to using Goal Seek ..., is that calculation of the x, y
coordinates of the intersection occurs automatically, "in the background." If you
change one or more of the parameters (for example, if you change the slope of
the straight line), the new intersection point and new drop line will be calculated
and displayed on the chart.

Using the Newton-Raphson Method
to Find Multiple Intersections
of a Straight Line and a Curve
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.