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