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:
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.
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