To Find the Point of Intersection of Two Curves
The NewtonRaphson 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 85 becomes x2 = (mx\ y\+ y2)/m (838)
You can set up the calculation in the same way that was used for the NewtonRaphson method with intentional circular reference. In the following example we will find the intersection of a straight line and a curve (Figure 834).
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 835. Portion of the data table for Figure 832. (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')
Figure 835. Portion of the data table for Figure 832. (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 NewtonRaphson 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 836. Using the NewtonRaphson method to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')
Figure 836. Using the NewtonRaphson method to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Using Circular Reference')
Figure 836 shows the cells where the NewtonRaphson calculation is performed, using an intentional circular reference (refer to the section "The NewtonRaphson 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 NewtonRaphson 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.
Responses

FREQALSI7 years ago
 Reply

tony jyrki7 years ago
 Reply

Mattiesko6 years ago
 Reply

ross taylor6 years ago
 Reply

gabriel6 years ago
 Reply

brian6 years ago
 Reply

Sam6 years ago
 Reply

lia6 years ago
 Reply

Luca6 years ago
 Reply

emilia6 years ago
 Reply

Ceredic6 years ago
 Reply

thomas6 years ago
 Reply

Mikko5 years ago
 Reply

Dahlak Kinfe5 years ago
 Reply

bernd5 years ago
 Reply

mosco4 years ago
 Reply

Ralph4 years ago
 Reply

Jessica4 years ago
 Reply

impi lehtim4 years ago
 Reply

amanda4 years ago
 Reply

carol4 years ago
 Reply

Patryk Thomson3 years ago
 Reply

eleleta3 years ago
 Reply