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 = m1x + c1 and line2:y1 = m2x + c2 wherem1, c1 are the slope and intersection of line1 andm2, c2 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 = m1a + c1 i.e. andb = m2a + c2.
Step 2: Equating the values of b, to find the value of a,m1a+ c1 = m2 + c2. .
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 = m1 + c1.
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.
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.
Similar Reads
How to Find the Intersection Between a Curve and Straight Line in Excel?
When two lines or curves having two different equations intersect with each other at one common point is known as Intersection. We can represent the point of intersection in Excel and also we can find the point of intersection using Excel functions SLOPE and INTERCEPT. Behind the intersection of a c
2 min read
How to Find the Intersection of Two Curves in Excel?
The intersection of the two curves is the point where the two curves meet, and their coordinates are the same. In this article, we will learn about how to find the intersection point of the two curves in excel. Finding the intersection point of two curves can be very useful in data analysis. Let's l
3 min read
Forms of Two-Variable Linear Equations - Straight Lines | Class 11 Maths
Line is the simplest geometrical shape. It has no endpoints and extends in both directions till infinity. The word “straight†simply means without “bendâ€. The gradient between any two point on the line is same. Hence, we can say that, if the gradient between any two points on the line is same, then
5 min read
What is the Point of Intersection of Two Lines Formula?
If we consider two lines a1x + b1y + c1 = 0 and a2x + b2y + c2 = 0, the point of intersection of these two lines is given by the formula:(x, y) = \left( \frac{b_1 c_2 \ - \ b_2 c_1}{a_1 b_2 \ - \ a_2 b_1}, \frac{c_1 a_2 \ - \ c_2 a_1}{a_1 b_2 \ - \ a_2 b_1} \right),The given illustration shows the i
5 min read
How to find the Equation of a Straight Line?
Geometry is a field of mathematics that deals with the size, position, shapes, angles, and dimensions of various things. 2D geometry includes shapes like squares, circles, triangles, hexagons, etc .2D shapes only have 2 dimensions. 3D geometry includes shapes like cubes, cylinders, cones, etc.3D sha
9 min read
Line Intersection in C
Determining the intersection point of the two lines is one of the fundamental problem in the computational geometry. This problem arises in various fields such as the computer graphics, robotics and geographic information systems. In this article, we will learn how to find whether two lines intersec
3 min read
Line Intersection in C++
Line Intersection in C++ is a problem that involves finding the point of intersection of two lines. For given points X and Y that corresponds to line XY and points A and B that corresponds to line AB the task is to find whether two lines (XY and AB) intersect and find the intersection point if they
4 min read
CSES Solution - Line Segment Intersection
There are two line segments: the first goes through the points (x1,y1) and (x2,y2), and the second goes through the points (x3,y3) and (x4,y4). Your task is to determine if the line segments intersect, i.e., they have at least one common point. Example: Input: points = {1, 1}, {5, 3}, {1, 2}, {4, 3}
9 min read
Linear Interpolation in Excel - 10 Methods with Example
Linear interpolation in Excel is a powerful method for estimating unknown values between two data points within a dataset. Imagine working with financial models, engineering calculations, or scientific data where you need to predict or fill in missing values with precision. Linear interpolation allo
15+ min read
How to Find the Y-intercept of a Line of a Graph in Excel?
A Y-intercept is a point on the line when x is equal to zero. It is more generally used in the slope-intercept form of the line. Let's learn, how to find the Y-intercept of a line of a graph in excel. Among many different forms of a line, the most commonly used line equation is the slope-intercept f
4 min read