Page 199 - Excel for Scientists and Engineers: Numerical Methods

176 EXCEL: NUMERICAL METHODS

This approach is very simple, but it has one major drawback-you must run
Goal Seek.. . each time you want to find the point of intersection. A much more
satisfactory approach is to use the Newton-Raphson technique to find the
intersection point, as illustrated in the following section.
The "drop line" in Figure 8-31 was added to the chart to emphasize the
intersection point. The line was added to the chart in the following way: cell A25
contains the formula =A24 and cell B25 contains the value 0. The highlighted
cells A24:B25 were copied and pasted in the chart to create a new series, as
follows: Copy A24:B25, activate the chart, choose Paste Special from the Edit
menu, check the boxes for Add Cells As New Series and X Values In First
Column, press OK. Figure 8-33 shows the portion of the worksheet where the
drop line is specified.

Figure 8-33. Adding a "drop line" from the intersection of two lines.
(folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')

Using the Newton-Raphson Method
to Find the Point of Intersection of Two Curves
The Newton-Raphson method can be modified to find the x value that makes
a function have a specified value, instead of the zero value that was used in a
previous section. Equation 8-5 becomes

x2 = (mxl -y1 +y2)/m (8-38)
You can set up the calculation in the same way that was used for the Newton-
Raphson method with intentional circular reference. In the following example
we will find the intersection of a straight line and a curve (Figure 8-34).