Calculate a Circle Center from Three 3D Points in Excel
Question details
Calculate the center and radius of the circle passing through three points in three-dimensional space.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has three 3D coordinates and wants to calculate their circle's center and radius with formulas or VBA.
- Observed behavior
- The desired result is a 3D center coordinate and a radius for the circle passing through all three points.
Arrange the three points in rows with X, Y, and Z coordinates in separate columns. The points must be distinct and noncollinear; otherwise, they do not define a unique circle.
Calculate the 3D circle with an Excel VBA function
Use a reusable VBA function to return the center coordinates and radius from a 3-by-3 range.
The function uses vector subtraction, dot products, and cross products. It returns an error for invalid input or points that are collinear or nearly collinear.
Enter the three points in A2:C4, one point per row. For the example, enter 1, 3, 1 in row 2; 3, 2, 0 in row 3; and 0, 0, 0 in row 4.
Open the VBA editor with Alt+F11, choose Insert > Module, and paste this code: Function Circle3D(P As Range) As Variant Dim u(1 To 3) As Double, v(1 To 3) As Double Dim n(1 To 3) As Double, c(1 To 3) As Double Dim a As Double, b As Double, nn As Double Dim result(1 To 1, 1 To 4) As Variant Dim i As Long On Error GoTo InvalidInput If P.Areas.Count <> 1 Or P.Rows.Count <> 3 Or P.Columns.Count <> 3 Then GoTo InvalidInput For i = 1 To 3 If Not IsNumeric(P.Cells(1, i).Value) Or Not IsNumeric(P.Cells(2, i).Value) Or Not IsNumeric(P.Cells(3, i).Value) Then GoTo InvalidInput u(i) = CDbl(P.Cells(2, i).Value) - CDbl(P.Cells(1, i).Value) v(i) = CDbl(P.Cells(3, i).Value) - CDbl(P.Cells(1, i).Value) a = a + u(i) * u(i) b = b + v(i) * v(i) Next i n(1) = u(2) * v(3) - u(3) * v(2) n(2) = u(3) * v(1) - u(1) * v(3) n(3) = u(1) * v(2) - u(2) * v(1) nn = n(1) * n(1) + n(2) * n(2) + n(3) * n(3) If a = 0 Or b = 0 Or nn <= 0.000000000001 * a * b Then GoTo InvalidInput c(1) = P.Cells(1, 1).Value + (a * (v(2) * n(3) - v(3) * n(2)) + b * (n(2) * u(3) - n(3) * u(2))) / (2 * nn) c(2) = P.Cells(1, 2).Value + (a * (v(3) * n(1) - v(1) * n(3)) + b * (n(3) * u(1) - n(1) * u(3))) / (2 * nn) c(3) = P.Cells(1, 3).Value + (a * (v(1) * n(2) - v(2) * n(1)) + b * (n(1) * u(2) - n(2) * u(1))) / (2 * nn) For i = 1 To 3 result(1, i) = c(i) Next i result(1, 4) = Sqr((c(1) - P.Cells(1, 1).Value) ^ 2 + (c(2) - P.Cells(1, 2).Value) ^ 2 + (c(3) - P.Cells(1, 3).Value) ^ 2) Circle3D = result Exit Function InvalidInput: Circle3D = CVErr(xlErrNum) End Function
Return to the worksheet and enter =Circle3D(A2:C4) in E2. In Excel versions with dynamic arrays, the four results spill across E2:H2. In older versions, select E2:H2 first, enter the formula, and press Ctrl+Shift+Enter.
For the sample points, check that E2:G2 show approximately 1.2741935, 1.3387097, and 0.2096774, and H2 shows a radius of approximately 1.8600208. Use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm) to retain the function.

Calculate the 3D circle in WPS Spreadsheet
Use worksheet formulas in WPS Spreadsheet to calculate the center and radius without a VBA function.
- 1. Enter the points: In WPS Spreadsheet, enter the three X, Y, Z coordinates in A2:C4, one point per row.
- 2. Calculate the direction vectors: Enter =A3-A2 in H2 and =A4-A2 in H3. Select H2:H3 and drag the fill handle across to J2:J3.
- 3. Calculate the cross product: Enter =I2*J3-J2*I3 in H4, =J2*H3-H2*J3 in I4, and =H2*I3-I2*H3 in J4. Enter =SUMSQ(H2:J2) in H5, =SUMSQ(H3:J3) in I5, and =SUMSQ(H4:J4) in J5.
- 4. Calculate the center: Enter these formulas: H7: =IF(OR(H5=0,I5=0,J5<=1E-12*H5*I5),NA(),A2+(H5*(I3*J4-J3*I4)+I5*(I4*J2-J4*I2))/(2*J5)); I7: =IF(OR(H5=0,I5=0,J5<=1E-12*H5*I5),NA(),B2+(H5*(J3*H4-H3*J4)+I5*(J4*H2-H4*J2))/(2*J5)); J7: =IF(OR(H5=0,I5=0,J5<=1E-12*H5*I5),NA(),C2+(H5*(H3*I4-I3*H4)+I5*(H4*I2-I4*H2))/(2*J5)).
- 5. Calculate the radius: Enter =SQRT((H7-A2)^2+(I7-B2)^2+(J7-C2)^2) in K7. Cells H7:J7 contain the center coordinates, and K7 contains the radius.

Frequently Asked Questions
What happens if the three points are collinear?
Collinear points do not define a unique circle. The VBA function returns #NUM!, and the WPS formulas return #N/A when the points are collinear or nearly collinear.
Why does the VBA function return only one value?
In Excel with dynamic arrays, enter =Circle3D(A2:C4) in a cell with three empty cells to its right. In older Excel versions, select four horizontal cells before entering the formula and press Ctrl+Shift+Enter.
Do three 3D points define a sphere as well as a circle?
Three noncollinear points define a unique circle in their plane, but they do not define a unique sphere. Four noncoplanar points are needed to define a unique sphere.
How can I check the calculated center?
Calculate the distance from the center to each of the three points using the 3D distance formula. All three distances should equal the calculated radius, allowing for rounding.




