Excel : Intersection between curve and straight line

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017


To find intersection of curve and a straight line we first need to know the mathematical condition behind it. 

When two lines cross:
  1. Before intersection, value of y1 is less than y2 at given value of x
  2. After intersection, value of y1 is less than y2 at next level of x value (xi+1)


I consider two equations : y1=2x+5  and y2=x^2+1 . On the basis of these equations i created data for three variables that are x, y1 and y2.

To find the intersection point :

STEP I

Calculate the difference between y1 and y2 at every level of x  

STEP II

Compare the values of Y for the two lines in every successive row. 

                  =IF(SIGN(F6*F7)=1,0,1)

STEP III: 

Identify the x values between which the lines have crossed

                 =MATCH(1,G6:G15,0)

STEP IV: 

Calculate X's and Y's data points

                =INDEX(C$6:C$15,H6)

STEP V: 

Find slope and intercept using the above data points

             =SLOPE(J$6:J$7,$I$6:$I$7)

             =INTERCEPT(J$6:J$7,$I$6:$I$7)

STEP VI: 

 To find the X-point at which  Y1<Y2 beomes Y2<Y1 :
              y1=m1x1+c1 and y2=m2x2+c2
Where m1: Gradient or Slope of line1, c1: Intercept of line1, m2: Gradient or Slope of line2, c2: Intercept of line2.

Since at the point of intersection, the two equations (form y=mx+c) will have the same values of x and y, we set the two equations equal to each other. This gives an equation that we can solve for x .We substitute that x value in one of the line equations and solve it for y

          Y= (c2*m1-c1*m2)/(m1-m2) ,
  
          X=(Y-m2)/c2    


STEP VII: 

Insert Scatter with Straight lines chart and mark intersection point 
( Right click on lines >> Format Data series >> Marker Options / Fill )
Click on the link below to download the excel file 
Intersection between lines

Related link : Intersection of two linear straight lines

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

4 Responses to "Excel : Intersection between curve and straight line"

  1. This only works if the intersection points are exactly alike and not if a line is interpolated. Just my 2 cents

    ReplyDelete
  2. What if I don't know the equation of the curve and the graph is generated through given data points?

    ReplyDelete
  3. Simply Marvelous! Excellent Job

    ReplyDelete

Next → ← Prev