Calculate Intersect of 2 lines

[theta]

Could somebody please assist me with this. It can be formula based or use VBA (or both).

I have a given line constructed of 2 sets of (x,y) co-ordinates. Let's call this LINE 1.

I have a distant point defined by 1 set of (x,y) co-ordinates. I also have an angle of travel from this point. Let's call the desired result LINE 2.

Assuming LINE 1 remains constant, how can I calculate the intercept of LINE 1 and LINE 2 given only the 4 pieces of information above?

If LINE 1 and LINE 2 would not intercept (because LINE 1 is too short), I would like the formula to still calculate at what point they WOULD intersect if LINE 1 extended to infinity.



(There is something similar here but no excel or VBA explanation http://stackoverflow.com/questions/27760826/line-intersection-with-a-difference-excel-vba)

 

[Special-K99]

You have a set of x,y coordinates for LINE 1 - but you havent told us what they are.
You have a set of x,y coordinates for LINE 2 - but you havent told us what they are.
You have an angle of travel - but againyou havent told us what it is.

Do you just want someone to say they can assist you?
Or are you actually looking for the answer in which case you'll need to supply the values that you have.

My psychic abilities dont work on a Friday.

 

[shg]

A line can be expressed parametrically as a point and a scalar value times its direction vector, i.e.,

Line = Point + Parameter * Direction

If you set two lines equal, you can calculate the parameter values (s and t below) where that occurs.

That's what this does.

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td="bgcolor:#F3F3F3"]

y​

[/td][td="bgcolor:#F3F3F3"]

angle​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Point1[/td][td]

3​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Point2[/td][td]

13​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]Dir[/td][td="bgcolor:#E5E5E5"]

10.000​

[/td][td="bgcolor:#E5E5E5"]

1.000​

[/td][td][/td][td][/td][td]B4:C4: {=B3:C3 - B2:C2}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Point3[/td][td]

23​

[/td][td]

16​

[/td][td]

195​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Dir[/td][td="bgcolor:#E5E5E5"]

-0.259​

[/td][td="bgcolor:#E5E5E5"]

-0.966​

[/td][td][/td][td][/td][td]B7: =SIN(RADIANS(D6))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]C7: =COS(RADIANS(D6))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

s​

[/td][td="bgcolor:#E5E5E5"]

1.70​

[/td][td][/td][td][/td][td][/td][td]B10:B11: {=MMULT(MINVERSE(CHOOSE({1,2;3,4}, B4, -B7, C4, -C7)), CHOOSE({1;2}, B6-B2, C6-C2))}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

t​

[/td][td="bgcolor:#E5E5E5"]

11.70​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Check1[/td][td="bgcolor:#E5E5E5"]

19.971​

[/td][td="bgcolor:#E5E5E5"]

4.697​

[/td][td][/td][td][/td][td]B12:C12: {=B2:C2 + B9 * B4:C4}[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Check2[/td][td="bgcolor:#E5E5E5"]

19.971​

[/td][td="bgcolor:#E5E5E5"]

4.697​

[/td][td][/td][td][/td][td]B13:C13: {=B6:C6 + B10 * B7:C7}[/td][/tr]
[/table]

 

[shg]

Or, via UDF,

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

x​

[/td][td="bgcolor:#F3F3F3"]

y​

[/td][td="bgcolor:#F3F3F3"]

angle​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Point1[/td][td]

3​

[/td][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Point2[/td][td]

13​

[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]Point3[/td][td]

23​

[/td][td]

16​

[/td][td]

195​

[/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]Dir[/td][td="bgcolor:#E5E5E5"]

-0.259​

[/td][td="bgcolor:#E5E5E5"]

-0.966​

[/td][td][/td][td][/td][td]B6: =SIN(RADIANS(D5))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]C7: =COS(RADIANS(D6))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Isect[/td][td="bgcolor:#E5E5E5"]

19.971​

[/td][td="bgcolor:#E5E5E5"]

4.697​

[/td][td][/td][td][/td][td]B9:C9: {=Isect(B2,C2, B3,C3, FALSE, B5,C5, B6,C6, TRUE)}[/td][/tr]
[/table]

Note that the direction angle is interpreted as compass heading (CW from north), not as the classic trig (CCW from east).

Function Isect(x1 As Double, y1 As Double, _
               ByVal x2 As Double, ByVal y2 As Double, Pt2IsDir As Boolean, _
               x3 As Double, y3 As Double, _
               ByVal x4 As Double, ByVal y4 As Double, Pt4IsDir As Boolean) As Variant

  ' shg 2014
  ' UDF or VBA

  ' Returns the point of intersection of two lines as the two-element variant {x,y}
  ' Pt2IsDir = True  =>  means {x2, y2} is a direction vector
  ' Pt2IsDir = False =>  means {x2, y2} is a second point on the line
  ' ... and similarly for Pt4IsDir

  ' Return #DIV/0! if the lines are coincident or parallel.

  Dim s             As Double
  Dim v             As Variant

  If Pt2IsDir Then
    x2 = x2 + x1
    y2 = y2 + y1
  End If

  If Pt4IsDir Then
    x4 = x4 + x3
    y4 = y4 + y3
  End If

  v = IsectPrams(x1, y1, x2, y2, x3, y3, x4, y4)
  If VarType(v) = vbBoolean Then
    Isect = CVErr(xlErrDiv0)
  Else
    s = v(0)
    Isect = VBA.Array(x1 + s * (x2 - x1), y1 + s * (y2 - y1))
  End If
End Function

Function IsectPrams(x1 As Double, y1 As Double, _
                    x2 As Double, y2 As Double, _
                    x3 As Double, y3 As Double, _
                    x4 As Double, y4 As Double) As Variant

  ' shg 2013

  Dim d             As Double   ' denominator of s and t

  d = (x4 - x3) * (y2 - y1) - (x2 - x1) * (y4 - y3)

  If d = 0 Then
    IsectPrams = False
  Else
    IsectPrams = VBA.Array(((x4 - x3) * (y3 - y1) - (x3 - x1) * (y4 - y3)) / d, _
                           ((x2 - x1) * (y3 - y1) - (x3 - x1) * (y2 - y1)) / d)
  End If
End Function

 

[theta]

shb- This is perfect, thank you.

Do you have any other formulas or UDF to calculate simple differences (I saw one commented in your UDF).

In the example shown, the two lines do not intersect but 'would' at the calculated X/Y coordinates. What is the best way to calculate the line length between the end of Line 1 and the calculated intersect?

I could do it with formulas but it would be messy - effectively finding the hyp by calculating the difference in adj and opp.

 

[shg]

Look at the function IsectPrams. If the returned value of both parameters are between 0 and 1, the lines intersect between their endpoints.