Following a discussion at the Eng-Tips Forum, I have written an Excel User Defined Function (UDF) to find the intersection point of two 3D line segments. The UDF has been added to the IP2 spreadsheet, which is available for free download (including full open source code) from IP2.ZIP.
The method of calculation of the intersection point is:
- Check if either End of Line2 is coincident with either end of Line1.
- Check if either end of Line2 lies on Line1.
- Check for quick 2D solution, if both lines lie on a plane parallel to the XY, XZ or YZ planes.
- Check that lines lie in the same plane, i.e distance of End2 of Line2 from the plane formed by Line1 and End1 of Line2 is zero.
- Find 2D IP of lines projected on to XY, XZ, and YZ planes.
- Check that the IP is within the length of each line segment.
- Extract the IP X,Y, and Z coordinates and assign to the function return value.
The screen shots below show for two 3D line segments:
The intersection point found using the new UDF (IP3D):
IP3D example
On spreadsheet calculation for the same two lines, using the UDF IP() (Example 2), and using only built in Excel Functions (Example 3)
On spreadsheet calculation of IP
and graphs of the two lines projected on to the three axis planes:
Projected intersecting lines
Newton Excel Bach, not (just) an Excel Blog 
Pingback: More 3D Intersections and 3D plotting function | Newton Excel Bach, not (just) an Excel Blog
Interesting problem. It’s not too difficult to extend to the question of finding the nearest points on two lines in 3D space. Given two pairs of 3×1 vectors of points on each line A,B and C.,D, array-enter in two 3×1 ranges:
=A+(B-A)*S
=C.+(D-C.)*T
where S,T are the least squares estimates that minimise the distance between the lines, i.e.:
=LINEST(C.-A,(B-A)*{0;1}+(C.-D)*{1;0},0)
Matrix operations can greatly simplify these type of calculations. It’s fun to try to extend to higher dimensions too, I’ve sent a hypercube example that might be of interest.
PS. The vba code isn’t compiling for me in 64-bit office 2010 version, probably due to a 32-bit windows api call.
LikeLike
Hi Lori – your post got sent to spam for some reason. Don’t know why, the filter is usually spot on.
Where did you send the hypercube? I haven’t received anything.
You might try deleteing the timer class from the VBA, I think that would be the only thing with any api calls.
LikeLike
Pingback: Alternative IP and Rotations | Newton Excel Bach, not (just) an Excel Blog
While trying to get intersection point of lines
Line 1 X Y Z
A -3000 2000 0
B 2000 4000 0
Line 2 X Y Z
C 3000 2000 0
D -2000 4000 0
I am getting
Method 1
IP3D Function:
X Y Z
No IP No IP No IP
Method 2
1. Find IP of lines projected onto each plane through the origin using IP function
X Y Z
XY Plane: 0 3200
XZ Plane No IP No IP
YZ Plane No IP No IP
Two IP’s found? FALSE FALSE FALSE
IP’s equal?
Lines intersect? TRUE
X Y Z
Intersection point 0 3200 No IP
Method 2 — Z should be zero.
Method 1 – Some modification required…
Thanks
Nitin Patel
LikeLike
Nitin – if the lines are in the XY plane the method won’t work, but you can just use a 2D intersection function in that case.
LikeLike
Pingback: Daily Download 16: Intersections, interpolations, and rotations | Newton Excel Bach, not (just) an Excel Blog