VBA & Macro Problems

Calculate a Circle Center from Three 3D Points in Excel

Rana GarciaRana Garcia Nov 22, 2026 868 views

Question details

Calculate the center and radius of the circle passing through three points in three-dimensional space.

Calculate a Circle Center from Three 3D Points in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the coordinates

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.

2
Add the VBA function

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

3
Enter the 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.

4
Check the result and save

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 with an Excel VBA function
Enable macros when appropriate: The function requires VBA support and enabled macros. Only enable macros in workbooks you trust.
Calculate without VBA

Calculate the 3D circle in WPS Spreadsheet

Use worksheet formulas in WPS Spreadsheet to calculate the center and radius without a VBA function.

  1. 1. Enter the points: In WPS Spreadsheet, enter the three X, Y, Z coordinates in A2:C4, one point per row.
  2. 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. 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. 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. 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.
Calculate a 3D circle from three points using worksheet formulasOpen and save Microsoft Excel .xlsx workbooksUse a familiar spreadsheet interfaceGet started with the free version of WPS Office
microsoft office alternative - wps office

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.