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.

### Like this:

Like Loading...

*Related*

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

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

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

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!

LikeLike

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

=arccenp3(D41:F41,D43:F43,D42:F42)

I get the correct result

RP3C 827977.932 815180.432 -3.111 0.160

LikeLike