How to Find Intersection of Two Lines in Google Sheets?

The Mathematical Foundation of Line Intersection

Before diving into the formulas used in Google Sheets, it is vital to recall the standard form of a straight line, known as the slope-intercept form: y = mx + b. In this equation, m represents the slope (or gradient) of the line, and b represents the y-intercept (the point where the line crosses the y-axis).

When seeking the intersection point of two distinct lines, Line 1 (y1 = m1x + b1) and Line 2 (y2 = m2x + b2), we are looking for the specific coordinate (x, y) where y1 equals y2. Therefore, we set the equations equal to each other: m1x + b1 = m2x + b2. Solving this system of linear equations yields the intersection point.

The resulting formulas used in Google Sheets automate this algebraic process, deriving the slope and intercept values directly from your raw data arrays (x and y values) and then solving for the unknown x and y values of the intersection.

Essential Google Sheets Functions for Calculation

To calculate the point of intersection, we must first establish the specific characteristics (slope and intercept) for each line based on the provided data. Google Sheets provides two primary statistical functions for this purpose, designed to perform linear regression analysis on datasets:

• SLOPE(known_y’s, known_x’s): This function returns the slope of the linear regression line through the data points in the specified arrays. It is equivalent to m in the standard equation.
• INTERCEPT(known_y’s, known_x’s): This function calculates the point at which the line will intersect the y-axis, using the existing data points. It is equivalent to b in the standard equation.

These functions allow us to treat our arrays of coordinates as the input for finding the parameters of the two intersecting lines (Line 1: m1, b1; Line 2: m2, b2). If the two lines are parallel (meaning their slopes are identical, m1 = m2), they will never intersect, and the division step in the X-value formula will result in an error, correctly indicating no solution.

Deriving the Formula for the X-Value of Intersection

The formula for finding the X-coordinate of the point of intersection is derived by rearranging the equation m1x + b1 = m2x + b2 to solve for x. When we substitute the SLOPE function for the slope (m) and the INTERCEPT function for the intercept (b), we get the comprehensive formula required for Google Sheets.

The resulting expression is complex but represents a direct implementation of the algebraic solution x = (b2 – b1) / (m1 – m2). It is essential to ensure that the array references for Line 1 (x1, y1) and Line 2 (x2, y2) are consistently mapped to the correct functions (e.g., using x1/y1 for m1 and b1, and x2/y2 for m2 and b2).

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 the first and second line, respectively, and y1 and y2 represent the corresponding array of y-values for each line. Pay close attention to the order of operations and the pairing of the arrays within the INTERCEPT function and SLOPE function calls.

Calculating the Y-Value of Intersection

Once the X-coordinate of the intersection point is determined, finding the corresponding Y-coordinate becomes a straightforward substitution problem. We simply plug the calculated X-value back into the linear equation for either Line 1 or Line 2. Since the intersection point lies on both lines, using either equation must yield the identical Y-value.

We use the form y = mx + b. In Google Sheets, we use the previously calculated X-intercept value (stored in a cell, often referred to here as x_intercept) and combine it with the calculated slope and intercept for Line 1 (or Line 2). The formula provided below uses the parameters derived from Line 1 (y1, x1).

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)

This formula confirms the required Y-coordinate, completing the identification of the unique coordinates (X, Y) where the two data trends cross. The following step-by-step example shows how to use these derived formulas in a practical application within Google Sheets.

Step 1: Structuring and Entering Data for Each Line

The initial step requires carefully organizing the known data points for both lines. Each line must have corresponding pairs of X and Y values (known_x’s and known_y’s). It is best practice to structure the data using distinct columns for easy referencing in the formulas.

We will define the data for Line 1 using columns A (x1) and B (y1), and the data for Line 2 using columns D (x2) and E (y2). Although the number of data points can vary, having at least two points per line is necessary to define a unique line. For this example, we use four data points for each line.

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

As illustrated in the screenshot, the data is entered into cells A3:B6 for Line 1 and D3:E6 for Line 2. Maintaining this clean structure prevents errors when applying array references in the subsequent steps.

Step 2: Calculating the X-Value of Intersection

Using the comprehensive X-value formula derived from the algebraic solution x = (b2 – b1) / (m1 – m2), we can calculate the X-coordinate of the intersection point. We will input this formula into a dedicated results cell, for instance, cell B10, using the cell ranges established in Step 1.

Note how the formula explicitly references the arrays: the INTERCEPT function for Line 2 (E3:E6, D3:D6) and Line 1 (B3:B6, A3:A6) are subtracted in the numerator, while the SLOPE function for Line 1 and Line 2 are subtracted in the denominator. The color-coding in the formula helps visualize the array pairings.

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:

After execution, the calculated x-value of intersection turns out to be 1.5. This value is now stored and ready to be used for the final calculation of the Y-coordinate.

Step 3: Calculating the Y-Value of Intersection

With the X-value (1.5) determined and stored in cell B10, we proceed to calculate the corresponding Y-value. We will input the Y-value formula into cell B11, referencing the calculated X-value in B10 and the statistical parameters (slope and intercept) of Line 1 (A3:B6).

The formula mirrors the standard slope-intercept form y = m * x + b, where m is calculated using the SLOPE function on Line 1 data, x is cell B10, and b is calculated using the INTERCEPT function on Line 1 data. Although Line 2 could also be used, choosing Line 1 simplifies the array references.

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 resulting y-value of intersection turns out to be 3. We have successfully determined that the unique point of intersection is (1.5, 3).

Step 4: Visualizing and Validating the Intersection Point

The final, crucial step in any data analysis is validation. By visualizing the two lines on a scatter plot, we can geometrically confirm that the calculated coordinates (1.5, 3) are indeed the exact point where the two trends converge.

To perform this check, simply select all the data points (A3:B6 and D3:E6) and insert a Scatter Chart in Google Sheets. This visualization demonstrates the relationship between the two linear equations and provides a powerful visual confirmation of our calculated result.

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 graphical representation validates the mathematical calculation performed using the SLOPE function and INTERCEPT function in Google Sheets, confirming (1.5, 3) represents the point on the plot where the two lines intersect.