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

### Like this:

Like Loading...

*Related*

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