## Two new functions for IP.xls

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:

1. Read the 3D coordinates for three points on the circle
2. Translate Point 2 and Point 3 for an origin at Point 1
3. Find polar coordinates of Point 3
4. Rotate Points 2 and 3 about the Z axis so that Point 3 is on the XZ plane
5. Rotate Points 2 and 3 about the Y axis so that Point 3 is on the X axis
6. Find angle of Point 2 from XY plane
7. Rotate Points 2 about the X axis so that Point 2 is on the XY plane
8. Find the XY coordinates of the mid-points of lines 1-2 and 3-2.
9. Find a second point on the perpendiculars through mid-points
10. Find the XY coordinates of the intersection of the perpendiculars.  This is the centre of the circle.
11. Find the radius of the circle
12. Rotate and translate the centre point back to the original axes
13. 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:

1. 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.
2. Find the slope, and then the intersection point, of the perpendiculars through the two tangents.  This is the centre of the circle.
3. 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

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

### 8 Responses to Two new functions for IP.xls

1. George says:

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!!

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!!

Like

• dougaj4 says:

George – Glad you found it useful. I’ll try and find time to add some explanation in the next few days.

Doug

Like

• George says:

Thanks for that Doug….. in your own time, absolutely no rush on my side.

Thanks again,
George

Like

• dougaj4 says:

Georg – see new note at the top, with link to a description of how the functions work, and updated IP2.wks spreadsheet.

Like

• dougaj4 says:

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.

Like

2. George says:

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

Like

• dougaj4 says:

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!

Like

• sjm says:

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!

Like