Microsoft Excel: How to find the intersection of two lines?

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.

Solution 1:

OK, one last post.

I feel for you if you are working it out only with basic arithmetic operations. Excel DOES have two functions which will shorten your formula quite a bit, though... still not "A1+V3"... AND make your formula a little easier to understand looking at it a year from now. Much easier to understand!

They are:

1) SLOPE()
2) INTERCEPT()

I will NOT belabor the math, just present the formula I would use. For it, I have assumed you put two points for the first line in A1 and A2 for the X's (A1:A2) and B1 and B2 for the Y's (B1:B2), then do similarly for the second line using A4:A5 and B4:B5. I put the formula for their intersection's X-value in D1 and their intersection's Y-value in E1.

The formula for the X-value (D1) is:

=IF(SLOPE(B1:B2,A1:A2)=SLOPE(B4:B5,A4:A5),    IF(INTERCEPT(B1:B2,A1:A2)=INTERCEPT(B4:B5,A4:A5),"Same line","Parallel lines"),    (INTERCEPT(B1:B2,A1:A2)-INTERCEPT(B4:B5,A4:A5))/(SLOPE(B4:B5,A4:A5)-SLOPE(B1:B2,A1:A2)))

(I broke it up a little for you with some spaces (works fine with them, but you can also remove them). First part checks to see if the slopes are the same. If they are, then the lines are either the same line, or they are parallel lines. The second part tests for that and tells you. If the slopes are NOT the same, the third part calculates the X-value of their intersection.)

The formula for the Y-value (E1) is:

=IF(T(D1)="",    ((INTERCEPT(B1:B2,A1:A2)-INTERCEPT(B4:B5,A4:A5))/(SLOPE(B4:B5,A4:A5)-SLOPE(B1:B2,A1:A2)))*SLOPE(B1:B2,A1:A2)+INTERCEPT(B1:B2,A1:A2),    D1)

(Same thing with the spaces.) This one uses T() to see if there is NOT text in D1. If there is not text in D1, the second part calculates their intersection's Y-value. If there actually is text in D1, the last little bit just repeats the same text as D1 has. You could replace that (the last little D1 at the end) with anything different that you wished. Perhaps you'd rather it be blank if D1 says "Same line" or "Parallel lines" so you might replace the D1 with "".

I did not arrange for any rounding or other display concerns.

(You could further shorten the formula if you were willing to use Named Ranges. But then understanding it a year from now would require digging those out and examining THEIR formulas and that can be obnoxious too, so six of one, half dozen of the other sometimes. Myself, I love Named Ranges.)

Solution 2:

I just had to do this. I'm using the cell numbers from my sheet.

Populate these cells with your point data:

B12 = X1

C12 = Y1

B13 = X2

C13 = Y2

B14 = X3

C14 = Y3

B15 = X4

C15 = Y4

D12=SLOPE(C12:C13,B12:B13)

E12=INTERCEPT(C12:C13,B12:B13)

D14=SLOPE(C14:C15,B14:B15)

E14=INTERCEPT(C14:C15,B14:B15)

B18=(E14-E12)/(D12-D14) [Intersection X ]

C18=D12*B18+E12 [Intersection Y]