3D Intersections

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:

  1. Check if either End of Line2 is coincident with either end of Line1.
  2. Check if either end of Line2 lies on Line1.
  3. Check for quick 2D solution, if both lines lie on a plane parallel to the XY, XZ or YZ planes.
  4. 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.
  5. Find 2D IP of lines projected on to XY, XZ, and YZ planes.
  6. Check that the IP is within the length of each line segment.
  7. 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

This entry was posted in Coordinate Geometry, Excel, Maths, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

7 Responses to 3D Intersections

  1. Pingback: More 3D Intersections and 3D plotting function | Newton Excel Bach, not (just) an Excel Blog

  2. Lori Miller says:

    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:
    where S,T are the least squares estimates that minimise the distance between the lines, i.e.:

    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.


  3. dougaj4 says:

    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.


  4. Pingback: Alternative IP and Rotations | Newton Excel Bach, not (just) an Excel Blog

  5. Nitin Patel says:

    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…


    Nitin Patel


  6. dougaj4 says:

    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.


  7. Pingback: Daily Download 16: Intersections, interpolations, and rotations | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s