Excel – How to find the intersection(/s) of two curves(non-linear and linear) in excel

microsoft excelmicrosoft-excel-2007

I have two plots using scatter plot of which one is non linear and the other one is linear. And I require the intersection of these curves. How should I proceed ?

Edit: The plots are done by using a set of values in excel.

Curve 1:

x: 0,
0.5,
1,
1.5,
2,
2.5,
3,
3.5,
4,
4.5,
5,
5.5,
6,
6.5,
7,
7.5,
8

y:8.43,
8.76,
8.27,
7.87,
7.69,
7.76,
8.46,
8.85,
8.34,
7.92,
7.73,
7.79,
8.42,
8.76,
8.27,
7.87,
7.69

Curve 2: y=8.168

Thanks

Edit-2: In the other question Get coordinates of intersecting point of two trend lines a trend line is made and then the intersection of those are dealt, which is clearly not possible for mine and not a duplicate for the question you are referring to.

Best Answer

EDIT: The following approach is applicable only to graphs where linear interpolation is appropriate and where the linear curve is a constant horizontal line.

Assuming your data is in columns A,B and C as shown below, the x-coordinate of the intersections can be found using the formula below. This formula filled down from D3 gives the results in the table below.

=IF(OR(AND($B2>=$C3,$B3<=$C3),AND($B2<=$C3,$B3>=$C3)),$A2+($A3-$A2)*($B2-$C3)/($B2-$B3),"")

enter image description here

If you would clearly explain your requirements, you might get an acceptable solution.

Here's the graph with a linear fit to the first curve (red line) and the second (constant) curve (purple line).

enter image description here

You can approach this in couple of ways:

  1. You can solve the equation of the linear fit for x when y = 8.168. That gives the point where the two straight lines cross (4.040, 8.168).

  2. You can find the points where the blue curve equals 8.168. The simplest way to do this is by linear interpolation, which assumes that the line segments between points can be approximated by a straight line. For the first intersection (between points 3 and 4) 8.168 is this fraction of the way between the two points:

    (8.27-8.168)/(8.27-7.87) = 0.255

And the x-coordinate is the same fraction of the way between 1 and 1.5, giving (1.128, 8.168).

The third crossing is coincidentally near the intersection with the linear fit, so let's see what it is, too:

(8.34-8.168)/(8.34-7.92) = 0.4095

and the third intersection is at (4.205, 8.168).

Related Question