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 IP2.zip

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

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.