microsoft excel
I want to find the intersection of two lines.
I have two points for the first line:
A(x1,y1) B(x2,y2)
and other two points for the second line:
C(x3,y3) D(x4,y4).
Is there an Excel formula that I can use to find the intersection of the two lines (the point that connects the first line with the second one)?
I tried to find a mathematical formula, but it's a little big, so I want to avoid this approach.
You cannot do what you want with Line Chart, you have to use Scatterplot.
EDIT: The following approach is applicable only to graphs where linear interpolation is appropriate and where the linear curve is a constant horizontal line.
Assuming your data is in columns A,B and C as shown below, the x-coordinate of the intersections can be found using the formula below. This formula filled down from D3 gives the results in the table below.
=IF(OR(AND($B2>=$C3,$B3<=$C3),AND($B2<=$C3,$B3>=$C3)),$A2+($A3-$A2)*($B2-$C3)/($B2-$B3),"")
If you would clearly explain your requirements, you might get an acceptable solution.
Here's the graph with a linear fit to the first curve (red line) and the second (constant) curve (purple line).
You can approach this in couple of ways:
You can find the points where the blue curve equals 8.168. The simplest way to do this is by linear interpolation, which assumes that the line segments between points can be approximated by a straight line. For the first intersection (between points 3 and 4) 8.168 is this fraction of the way between the two points:
(8.27-8.168)/(8.27-7.87) = 0.255
And the x-coordinate is the same fraction of the way between 1 and 1.5, giving (1.128, 8.168).
The third crossing is coincidentally near the intersection with the linear fit, so let's see what it is, too:
(8.34-8.168)/(8.34-7.92) = 0.4095
and the third intersection is at (4.205, 8.168).
Best Answer
Your problem is something I recall from algebra class many decades ago. It's called "solving an equation with two variables."
You'll want to convert your lines into formulae of the format "y = mx + b" in which m is the slope of the line and m is the y intercept. This aspect is solved at math.com, from which the following information is copied:
When you've converted the lines to this format, mathplanet.com has the next sequence of steps:
I don't think you'll find anything other than a mathematical formula, or a combination of formulae. I'm not sure what "a little big" means in this context, but one cannot avoid the math to solve this problem.
If your lines are parallel, you may run into a divide-by-zero error. If your lines are too short to intersect, you will get the "extended" intersection point as if the lines were of infinite length.
"math is fun!"