Edit 8 Feb 2012: The functions described below have been superseded by new versions which are described at https://newtonexcelbach.wordpress.com/2009/08/18/finding-circle-centres-in-3d/. That link also gives a description (with diagrams!) of how the functions work. The download file for the new versions is at IP2.zip.
In response to this thread at the Eng-Tips forum I have added two new functions to my IP.xls spreadsheet:
- ArcCenT2IP finds the centre and radius of an arc specified by 2 tangent points and the intersection point of the tangents.
- ArcCenP3 finds the centre and radius of an arc specified by any 3 points on the arc.
Full open source code for the two functions is included in the spreadsheet, which can be downloaded here:IP.zip
The procedure used is similar for both functions; for ArcCenP3:
- Read the 3D coordinates for three points on the circle
- Translate Point 2 and Point 3 for an origin at Point 1
- Find polar coordinates of Point 3
- Rotate Points 2 and 3 about the Z axis so that Point 3 is on the XZ plane
- Rotate Points 2 and 3 about the Y axis so that Point 3 is on the X axis
- Find angle of Point 2 from XY plane
- Rotate Points 2 about the X axis so that Point 2 is on the XY plane
- Find the XY coordinates of the mid-points of lines 1-2 and 3-2.
- Find a second point on the perpendiculars through mid-points
- Find the XY coordinates of the intersection of the perpendiculars. This is the centre of the circle.
- Find the radius of the circle
- Rotate and translate the centre point back to the original axes
- Assign the 3D coordinates of the circle centre, and the circle radius, to the function return value as a 1×4 array
ArcCenT2IP is very similar, except:
- The first step is to check that the two “tangent” points are equidistant from the intersection point. If not, adjust the coordinates of the further point to be an equal distance from the intersection point as the closer tangent point.
- Find the slope, and then the intersection point, of the perpendiculars through the two tangents. This is the centre of the circle.
- If either of the tangent points has been adjusted, return the adjusted coordinates, and the point number, as a second row of the 2×4 array function return value.
A screen shot of input and output for the two functions is shown below:
Functions to Find Circle Centre and Radius
Newton Excel Bach, not (just) an Excel Blog 
Hey Doug Jenkins,
Really great work on that “circle from three points” spreadsheet. How lucky did I feel when I stumbled on this which was exactly what I needed!!
I had a question about taking your ArcCenP3 a bit further.
What I want to do is after the centre point of the circle has been calculated, is then calculate a point perpendicular to the plane of the circle at an offset of 1.0m.
What I’m trying to do is understand what I’m looking at when you show all the working out step by step. I’ll also want to know how to calc a few other things from the plane of the circle.
So i guess what I’m asking is a bit of an explanation of what those matrixs are doing. Is there some information you could send me which will spell this out? And maybe point me in the right direction for calculating the plane of the circle?
Any and all help is appreciated!!
LikeLike
George – Glad you found it useful. I’ll try and find time to add some explanation in the next few days.
Doug
LikeLike
Thanks for that Doug….. in your own time, absolutely no rush on my side.
Thanks again,
George
LikeLike
Georg – see new note at the top, with link to a description of how the functions work, and updated IP2.wks spreadsheet.
I hope that link answers your question, but if not, please ask.
LikeLike
Georg – you might find this post of use as well:
https://newtonexcelbach.wordpress.com/2011/08/21/dots-and-crosses/
It includes vector functions for Excel, including one that will return the unit normal to a plane defined by three 3D points.
LikeLike
Thanks a lot for the extra help Doug. Those images and extra explanations have helped me wrap my mind around whats happening.
Much Appreciated!!
George
LikeLike
One of the benefits of doing a blog is that without it I never would have done the diagrams, and now I’d have no idea how the things worked. Having an audience is a great incentive to do a bit of documentation!
LikeLike
Thank you soo much for the effort you put into this. I have looked almost everywhere for the way to define the centre of a circle in 3D and nearly leaped for joy when finding your spreadsheets.
One thing that I am unsure of is how to find a best fit for many data points do you know how to do this in excel? We use Datscan to find points however we need to find the best fit of these 3D data points. I have heard that least squares minimization is helpful yet I can’t find an example of how to do it.
Thanks!
LikeLike