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.

Step 14: Press Enter. The y coordinate of the intersection point is 1.

Step 15: The intersection point of both lines is (1, 1). We can also see (1, 1) is the intersection point in the graph.

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.