It is a simple matter to use Goal Seek... to find the intersection of two lines, as illustrated in Figure 8-31
In the spreadsheet cells shown in Figure 8-32, 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 =B24-C24
Now use Goal Seek... to vary A24 to make the target cell, D24, equal to zero. The result is shown in Figure 8-32.
A |
B |
C |
D | |
22 23 |
Table for intersection x y1 y2 y1-y2 | |||
24 |
12 86 |
86 0 |
Figure 8-32. Using Goal Seek to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Figure 8-32. 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 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.
A |
B |
C D | |
22 |
Table for intersection &. for drop line | ||
23 |
X |
yi |
y 2 y1-y2 |
24 |
12 |
86 |
35 a |
25 |
12 |
0 |
Figure 8-33. Adding a "drop line" from the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Figure 8-33. Adding a "drop line" from the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')
Was this article helpful?