How to Find Intersection of Two Lines in Google Sheets


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

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 B10 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 B11 to find the y-value of intersection:

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

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

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, 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 Google Sheets:

How to Plot an Equation in Google Sheets
How to Find the Slope of a Trendline in Google Sheets
How to Perform Curve Fitting in Google Sheets

Leave a Reply

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