Best Fit Circle: find the center using Excel

Finding the center of a best fit circle depends on minimizing the same function we were concerned with in finding the radius, except that we are going to view it as a function of the center instead of as a function of the radius:

where a and b are the x- and y-coordinates, respectively, of the center and r is given by

What we have here is a function of two variables. It looks like three variables until you realize that r is calculated in terms of the other two. So, we can do a three dimensional plot and see what the scoop is. I used Maxima to do this and obtained a very good view of the surface near the best fit center of the points I have been using in all of my investigations of this problem. Here is the 3D plot of SSE(a,b):

What we are most encouraged to see in this graph is that it looks very smooth and it looks like there is exactly one point that is the lowest point. This lowest point is where the SSE function is minimized and constitutes the best center of the circle. (It might be that there are a few local minima somewhat close together that we could see if we zoomed up really tight to the bottom and we are probably happy with any of these as the "answer". Welcome to numerics.)

These formulae can be used in Excel. Designate two cells for each of the values a and b. You don't know what these are, but start with some guesses for these. You will reference these guesses in your Excel formulae. Put your points in consecutive rows after the pattern (x, y, se, R) where se references the x and y for that line as well as the values for a, b, and r. R will only reference x, y for that line and a, b from above. r above is the average of all the R values in the rows (don't include the 1/n in the R)--you may want to create a cell to contain this average and reference it in your se columns. Use absolute references for a, b, and r (if you have a cell for it) so you can copy and paste the formula easily. Make a sum formula at the bottom of your SSE column and it represents your SSE function as above. You want to use the Excel solver now. The SSE cell is the cell you tell it to minimize and the a and b cells you designate as the cells to be modified. The solver will tweak with the a and b values in an attempt to make SSE as small as possible. (The instructions about r and R might seem circular until you actually implement them. Follow through to the end and you'll see it really isn't circular.) Don't try too hard to follow the instructions--try to do the likely intent (as always).

For a Maxima approach see here.

Best Fit Circle: find radius given center

Given a center (a, b) of a circle, we want to find the best fit radius, r, to a set of given points P_i = (x_i , y_i), for i = 1 to n. By addressing the relationship between best radius for a given set of points, we will be able to make r dependent on a and b, rather than being something we guess at independently. (This is ground work for another post.)

In other words, we have a set of points and a candidate center for what we think is a good approximation to a circle (or a circular arc). We want to find the best radius for that center with those points and have a means of quantifying how good of a fit we have. If we can find this, we can decide which of a set of candidate centers is best.

The usual thing to do when looking for a best fit shape is to minimize the sum of squares of the errors. If we knew the radius we might calculate the sum of squares of the errors as

So, for each point, we find out how far it is from the candidate center, find the difference between that distance and the radius to get the error, and then square the error. And then we add all of those squared errors together to get SSE(r). We want to minimize SSE(r), which is a simple problem in differential calculus. When SSE’(r) = 0, SSE(r) is at a minimum, maximum, or perhaps a point of inflection. Then

Observe that the best fit radius is just the average distance from the candidate center to each point. Seems sensible. Also, observe that

and so, by the second derivative test, SSE(r) is at a minimum.

To see it all put together into a practical solution, see Best Fit Circle: find the center using Excel.