Intersection of Two Linear Straight Lines in Excel

Last Updated : 22 Aug, 2022

Intersection points can be beneficial in finding insights into the data, As intersections give the same values for different data sets. Excel can help to automate the task of finding the intersection point of two lines by using the =slope() and =intersection() function and replacing their values with the given equations. In this article, we will learn how to find the intersection point of two straight lines in excel.

Procedure to find Intersection

An intersection is a point where 2 curves have the same coordinates. The equation of a straight line can be written asy = mx + c, where m is the slope and c is the intercept of a line. For example, you are given two lines line1:y = m₁x + c₁ and line2:y₁ = m₂x + c₂ wherem₁, c₁are the slope and intersection of line1 andm_2,c₂ are the slope and intersection of line2. Considering the intersection point to be (a, b).

Following are the steps

Step 1: As, (a, b) is the intersection point of the two lines, which means that (a, b) satisfies the equation of both linesb = m₁a + c₁ i.e. andb = m₂a + c₂.

Step 2: Equating the values of b, to find the value of a,m₁a+ c₁ = m_{2 +}c_2. .

Step3: After rearranging the equation, the value of a comes out to be, $a = \frac{(c_2 - c_1)}{(m_1 - m_2)}$ .

Step 4: Now, substitute the value of a in any of the equations to find the value of b,b = m₁ + c₁.

Excel Functions used in Calculating Intersection

There are two excel functions that are helpful in calculating the intersection point of a line.

=SLOPE(y_values, x_values)

The slope function calculates the average slope of a dataset. The slope function takes two arguments, the first argument is the values of y, and the second argument is the values of x.

=INTERCEPT(y_values, x_values)

The intercept function calculates the average intercept of a dataset. The intercept function takes two arguments, the first argument is the values of y, and the second argument is the values of x.

Calculating the intersection of lines in Excel

Generally, the equation of lines is not given, but the data points of the equation are given. We will calculate the intersection using the same procedure as explained above. For example, “Arushi” is a data analyst, and she has been given two data sets, each containing values of x and y. “Arushi” had drawn the graph of the two lines, and she wanted to find the intersection of the lines drawn from the given data set.

Following are the steps

Step 1: Firstly, we need to find the slope and intersection of both the lines. To do this, make four columns, A9:A12, specifying the name of the intercept and slope of each line.

Step 2: Cell C9 has to be filled with the slope of the line1. Use =SLOPE() function, to calculate the average slope of the given data points, i.e. =SLOPE(B4:B6, A4:A6).

Step 3: Press Enter. The slope of the line1 is 1.

Step 4: Cell C10 has to be filled with the slope of the line2. Use =SLOPE() function, to calculate the average slope of the given data points, i.e. =SLOPE(E4:E6, D4:D6).

Step 5: Press Enter. The slope of the line2 is 2.

Step 6: Cell C11 has to be filled with the intercept of the line1. Use =INTERCEPT() function to calculate the average intercept of the given data points, i.e., =INERCEPT(B4:B6, A4:A6).

Step 7: Press Enter. The intercept of the line1 is 0.

Step 8: Cell C12 has to be filled with the intercept of the line2. Use =INTERCEPT() function to calculate the average intercept of the given data points, i.e., =INERCEPT(E4:E6, D4:D6).

Step 9: Press Enter. The intercept of the line2 is -1.

Step 10: Now, we have the slope and intercept of both the lines. Now, we have to find the intersection point of the two lines. To do this, make two columns, A15:A16, specifying the name of the point of co-ordinates of the intersection.

Step 11: Cell B15 has to be filled with the x coordinate of lines intersection. Use the formula as explained above to calculate the x co-ordinate of lines intersection.

Step 12: Press Enter. The x coordinate of the intersection point is 1.

Step 13: Cell B16 has to be filled with the y co-ordinate of lines intersection. Use the formula as explained above to calculate the y co-ordinate of lines intersection.