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 = (mx\ -y\+ 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).

Two Lines Intersection Excel
Figure 8-34. Finding the intersection of two lines in a chart, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')
A portion of the data table that generated the two lines is shown in Figure 835.

A

c I

4

X

yt

y2

5

0

100

-10

6

1

108

-3

7

2

116

6

"8

3

124

17

4

132

30

10

5:

140

45

Figure 8-35. Portion of the data table for Figure 8-32. (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')

Figure 8-35. Portion of the data table for Figure 8-32. (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')

The formula in cell B5 is =slope*A5+int and in cell C5

=aa*A5A2+bb*A5+cc

Using the same method as in the preceding section, y\ is the function for which the slope is calculated, and y2 is the value used as the "constant." Of course, both y\ and y2 change as the value of x changes.

A

B

C D

E F

G !

3£ 37

Using modified Newton-Raphson approach to find intersection x y1 y2 x+Ax y1+Ay slope new x

38

11.536

192.285

192.285 11.536

192.285 29.07130865

11.536

39

11.536

0

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

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.

Was this article helpful?

+1 -4

Responses

Post a comment