Finding Circle Centres in 3D

In 2 new functions for IP.xls I described two Excel User Defined Functions (UDFs) to find the radius and centre of a circle defined by 3D coordinates for either 3 points on the circumference, or for two tangent points and the intersection of the tangents.  The algorithm used had the merit of being intuitively obvious, but was unnecessarily long and complex.  I have now re-written the UDFs with a much more efficient algorithm, and added two more functions.  Download

The new functions are:

  • ArcCenTP requires a point on the circle, one point on the tangent line through that point, and one other point on the circle
  • ArcCenRP requires one point on the circle, one point on a radial line through that point, and one other point on the circle

The general method used in all the functions is:

  • From the coordinates of the three input points, ABC, find the angle BAC and the length of the line AC
  • Find the radius of the circle, R
  • Find the direction of a line parallel to a line between a known point and the circle centre
  • Project a line from this known point towards the circle centre

The four different cases are shown below:

ArcCen Functions, click to view full size

ArcCen Functions, click to view full size

Referring to the four cases shown above, the procedure is:

From input coordinates find:
  Direction cosines of AB
  Direction cosines of AC
  Cos BAC (from Cosine Rule)
  Sin BAC (Case 1 and 3)
Calculate R:
  Case 1: AE / Sin BAC
  Case 2: AD / Cos BAC
  Case 3: AD / Sin BAC
  Case 4: AD / Cos BAC
Find direction cosines of line parallel to line from a known point to centre:
  Case 1: DC
  Case 2: BD
  Case 3: DC
  Case 4: AB
Project from known point to circle centre:
  Case 1: Point E, mid point of AB
  Case 2: Point D, mid point of AC
  Case 3: Point A
  Case 4: Point A

All four functions return a four column array containing the XYZ coordinates of the circle centre and the radius of the circle.  To enter an array function:

  • Enter the function as usual
  • Select the cell containing the function and the three adjacent cells to the right
  • Press F2 to enter “edit mode”
  • Press Ctrl-Shift Enter

As, before the function ArcCenT2IP also returns a second line with the coordinates of the adjusted tangent point, if any.

Further notes are given in the download file, and as usual it includes full open source VBA code.

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

5 Responses to Finding Circle Centres in 3D

  1. Pingback: Maximum distance between two points | Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Two new functions for IP.xls | Newton Excel Bach, not (just) an Excel Blog

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

  4. sjm says:

    hey there!

    Thank you for the information so far, it has been extremely helpful.
    I have an additional question, do you know how to create a best fit for the circle and radius in 2D and or 3D (if possible) for >3 points. I appreciate any assistance!


  5. sidney says:

    I found that there will be error in ArcCen3 if one the angles in ABC is greater than 90 and the input sequence is in anti-clockwise.

    sample data as below
    D E F
    41 RP3P1 827977.774 815180.407 -3.111
    42 RP3P2 827978.090 815180.451 -3.111
    43 RP3P3 827977.956 815180.274 -3.112

    if I simply input =arccenp3(D41:F41,D42:F42,D43:F43)
    The results will be :
    RP3C 827977.774 815180.407 -3.111 0.000

    but when I change the input sequence to

    I get the correct result
    RP3C 827977.932 815180.432 -3.111 0.160


Leave a Reply

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

You are commenting using your 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