To Find the Point of Intersection of Two Lines
It is a simple matter to use Goal Seek... to find the intersection of two lines, as illustrated in Figure 831
In the spreadsheet cells shown in Figure 832, the formula in cell B24 is =slope1*A24+int1 and the formula in cell C24 is =slope2*A24+int2
Both formulas use A24 as input. The formula in cell D24 (the target cell) is =B24C24
Now use Goal Seek... to vary A24 to make the target cell, D24, equal to zero. The result is shown in Figure 832.
A 
B 
C 
D  
22 23 
Table for intersection x y1 y2 y1y2  
24 
12 86 
86 0 
Figure 832. Using Goal Seek to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Figure 832. Using Goal Seek to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
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 NewtonRaphson technique to find the intersection point, as illustrated in the following section.
The "drop line" in Figure 831 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 833 shows the portion of the worksheet where the drop line is specified.
A 
B 
C D  
22 
Table for intersection &. for drop line  
23 
X 
yi 
y 2 y1y2 
24 
12 
86 
35 a 
25 
12 
0 
Figure 833. Adding a "drop line" from the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Figure 833. Adding a "drop line" from the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Responses

klaus1 year ago
 Reply

bernd finkel11 months ago
 Reply

ralf11 months ago
 Reply