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 8-31

Find Intersection Excel
Figure 8-31. Finding the intersection of two lines in a chart, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')

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?

0 -3

Responses

  • bernd finkel
    What happens when two lines intersect?
    2 years ago
  • ralf
    How to find the point of intersection on exel?
    2 years ago
  • karen
    How to show intersection point in excel graph?
    7 months ago
  • lea
    How to find intersection points of two lines in excel?
    4 months ago
  • CAIN
    How to use goal seek to find the intersection?
    3 months ago
  • hamilcar
    How to get insection point of two straight line equations in excel?
    2 months ago
  • wilbert
    How to find intersection point of two lines using ecel?
    14 hours ago

Post a comment