How To Find Intersection Point In Excel Graph

To Find the Point of Intersection of 2 Lines

It is a elementary matter to use Goal Seek... to discover the intersection of ii lines, as illustrated in Figure eight-31

: Figure eight-31. Finding the intersection of two lines in a nautical chart, (binder 'Affiliate 08 Examples', workbook 'Intersecting Lines', canvass 'Two Direct Lines')

In the spreadsheet cells shown in Figure 8-32, the formula in cell B24 is =slope1*A24+int1 and the formula in jail cell C24 is =slope2*A24+int2

Both formulas use A24 as input. The formula in prison cell D24 (the target cell) is =B24-C24

At present utilize Goal Seek... to vary A24 to make the target cell, D24, equal to zero. The result is shown in Figure viii-32.

	A	B	C	D
22 23	Table for intersection x y1 y2 y1-y2
24	12 86		86 0

Effigy 8-32. Using Goal Seek to find the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Direct Lines')

Effigy 8-32. Using Goal Seek to notice the intersection of two lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet 'Two Straight Lines')

This approach is very uncomplicated, simply it has one major drawback—you must run Goal Seek... each time y'all want to find the point of intersection. A much more than satisfactory approach is to employ the Newton-Raphson technique to detect the intersection betoken, equally illustrated in the post-obit section.

The "driblet line" in Figure 8-31 was added to the chart to emphasize the intersection bespeak. The line was added to the chart in the following fashion: 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: Re-create A24:B25, activate the chart, cull Paste Special from the Edit menu, check the boxes for Add Cells As New Series and X Values In First Column, printing OK. Figure 8-33 shows the portion of the worksheet where the driblet 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 "drib line" from the intersection of 2 lines, (folder 'Chapter 08 Examples', workbook 'Intersecting Lines', sheet '2 Directly Lines')

Figure 8-33. Adding a "drop line" from the intersection of 2 lines, (folder 'Affiliate 08 Examples', workbook 'Intersecting Lines', sail 'Two Straight Lines')

Continue reading here: Bug

Was this article helpful?