How to Find the Intersection of Two Lines in Excel


You can use the following formulas to find the point of intersection of two straight lines in Excel:

Find the X-Value of Intersection:

=(INTERCEPT(y2,x2)-INTERCEPT(y1,x1))/(SLOPE(y1,x1)-SLOPE(y2,x2))

This formula assumes x1 and x2 represent an array of x-values for each line and y1 and y2 represent an array of y-values for each line.

Once you find this x-value, you can then plug that value into this formula to find the y-value of intersection:

Find the Y-Value of Intersection:

=SLOPE(y1,x1)*x_intercept+INTERCEPT(y1,x1)

The following step-by-step example shows how to use these formulas in practice.

Step 1: Enter the Values for Each Line

First, let’s enter the (x, y) coordinates for two lines:

Step 2: Find the X-Value of Intersection

Next, type the following formula into cell H2 to find the x-value of intersection:

=(INTERCEPT(E3:E6,D3:D6)-INTERCEPT(B3:B6,A3:A6))/(SLOPE(B3:B6,A3:A6)-SLOPE(E3:E6,D3:D6))

The following screenshot shows how to use this formula in practice:

The x-value of intersection turns out to be 1.5.

Step 3: Find the Y-Value of Intersection

Next, type the following formula into cell H3 to find the y-value of intersection:

=SLOPE(B3:B6,A3:A6)*H2+INTERCEPT(B3:B6,A3:A6)

The following screenshot shows how to use this formula in practice:

find intersection of two lines in Excel

The y-value of intersection turns out to be 3.

Step 4: Visualize the Intersection Point

If we plot each line on the same plot in Excel, we can see that the intersection point is indeed at the (x, y) coordinates of (1.5, 3):

This represents the point on the plot where the two lines intersect.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Solve a Quadratic Equation in Excel
How to Solve a System of Equations in Excel
How to Find Y-Intercept of a Graph in Excel

Featured Posts

One Reply to “How to Find the Intersection of Two Lines in Excel”

  1. Works great — provided neither line has constant X-values (i.e., a vertical line). If so you get a #DIV/0! error from the INTERCEPT and SLOPE formulae. So you just need to trap that and handle that special condition separately.

    Assuming BOTH lines aren’t vertical, for the Step 2 Formula in H2 you could use something like:

    =IF(D3=D4,D3,IF(A3=A4,A3,(INTERCEPT(E3:E6,D3:D6)-INTERCEPT(B3:B6,A3:A6))/(SLOPE(B3:B6,A3:A6)-SLOPE(E3:E6,D3:D6))))

    And for the Step 3 Formula in H3 you could use:

    =IF(D3=D4,SLOPE(B3:B6,A3:A6)*H2+INTERCEPT(B3:B6,A3:A6),SLOPE(E3:E6,D3:D6)*H2+INTERCEPT(E3:E6,D3:D6))

Leave a Reply

Your email address will not be published. Required fields are marked *