Diameter Formula in Excel

The diameter of a circle or a sphere is the length of the straight line connecting the center of the circle or sphere with two points on its perimeter or surface.

We can construct formulas in Excel to calculate the diameter if any of the following is known: radius of the circle or sphere, circumference of the circle, area of the circle, or volume of the sphere.

In this tutorial, we will learn how to create Excel formulas we can create to calculate the diameter given different situations.

The Radius of a Circle or Sphere Is Known

Radius is the length of the line segment between the center and circumference of a circle or sphere. The diameter is twice the length of the radius.

We use the following dataset with radiuses in column A to compute the diameter values in column B:

Use the PRODUCT function

We use the following steps:

1. Select cell B2 and enter the formula =PRODUCT(A2,2) as follows:

2. Press Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the formula

The PRODUCT function multiplies all the numbers given as arguments. In this case, the function multiplied the radius values in column A with value 2 and returned diameter values in column B.

Use User Defined Function (UDF)

Let’s create a User Defined Function in Excel VBA and use it to calculate the diameter.

We use the following steps:

1. In the active worksheet, press Alt + F11 to open the Visual Basic Editor.
2. In the Project Window right-click the workbook and insert a new module:

3. In the new module type in the following code:

4. Save the function procedure.
5. Save the workbook as a macro-enabled workbook.
6. Press Alt + F11 to switch back to the active worksheet.
7. Select cell B2 and type in the formula =RADIUStoDIAMETER(A2) as follows:

We may have noticed that immediately we begin typing in the formula, our UDF appears in the list of the other Excel functions:

We can double-click it or press the tab key to enter it instead of typing it in. This saves time.

8. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the User-Defined Function

• Function RADIUStoDIAMETER(RADIUS As Long) As Long. The function is named and its data type is Long. It takes one argument of the Long data type.
• RADIUStoDIAMETER = WorksheetFunction.Product(RADIUS * 2). The worksheet’s in-built PRODUCT function multiplies the argument, that is passed to it, by 2 and assigns the product to the RADIUStoDIAMETER variable. The RADIUStoDIAMETER function then returns this product as the diameter value.

Circumference of Circle or Sphere Is Known

The circumference is the length of the closed curve of a circle or sphere. It is equal to pi (3.1416) times the diameter. This means that we can get the diameter by dividing the circumference by pi.

We use the following dataset with circumference values in column A to compute the diameter values in column B:

We use the following steps:

1. Select cell B2.
2. Type in the formula =A2/PI() as follows:

3. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the formula

The value in cell A2 is divided by the value returned by the Excel in-built pi function.

Use a User Defined Function (UDF)

We can create a UDF using Excel VBA and use it to calculate diameter values given circumference values.

We follow the steps below:

1. Open the VBE and Insert a new module.
2. Type in the following function procedure:

3. Select cell B2 and key in the formula =CIRtoDIA(A2) as below:

4. Press Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the CIRtoDIA User-Defined Function

• Function CIRtoDIA(CIR As Long) As Long. The function is named CIRtoDIA (Circumference to Diameter). Its data type is Long. It takes one argument (CIR) of the Long data type.
• CIRtoDIA = CIR / WorksheetFunction.Pi. The value passed to the function during execution is stored in the CIR variable. It is then divided by the pi value which is returned by the in-built pi worksheet function. The result is assigned to the CIRtoDIA variable and it is the value returned by the CIRtoDIA function as the diameter value.

The area of the Circle Is Known

We use the following dataset with area values in column A to compute the diameter values in column B:

We use the following steps:

1. Select cell B2.
2. Type in the formula =SQRT((A2*4)/PI()) as follows:

3. Press Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the formula

The area of a circle is the product of the square of the diameter and Pi divided by 4. Area = D².π/4.

This means that the diameter is equal to the square root of four times the area divided by Pi.

In the formula, the area in cell A2 is multiplied by 4. The product is divided by the value returned by the Pi function. The resultant value is passed to the SQRT function which returns the square root as the diameter value.

Use a User-Defined Function

We create a UDF to use by using the following steps:

1. Open the VBEand insert a new module.
2. In the new module, type in the following function procedure:

3. Select cell B2 and type in the formula =AREAtoDIA(A2) as follows:

4. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the AREAtoDIA User-Defined Function

• Function AREAtoDIA(AREA As Long) As Long. The function is named AREAtoDIA (Area to Diameter). Its data type is Long. It takes one argument (AREA) of the Long data type.
• AREAtoDIA = Sqr((AREA * 4) / WorksheetFunction.Pi). The value passed to the function during execution is stored in the AREA variable. It is then multiplied by 4. The result is then divided by the value returned by the in-built Pi worksheet function. The result is passed to the in-built Sqr VBA function. The Sqr function returns the square root of the value which is the diameter of the circle or sphere.

The Volume of the Sphere Is Known

We use the following dataset with volume values in column A to compute the diameter values in column B:

We use the following steps:

1. Select cell B2 and type in the formula =((A2*6)/PI())^(1/3) as follows:

2. Press the Enter key and double-click or drag down the fill handle to copy the formula down the column:

Explanation of the formula

The diameter of a sphere is equal to the cube root of the volume multiplied by six and the product divided by Pi.

The formula implements this logic by:

• (A2*6). The volume in Cell A2 is multiplied by 6.
• (A2*6)/PI(). The product from the previous step is divided by the Pi value returned by the Pi function.
• ((A2*6)/PI())^(1/3). The cube root of the result from the previous step is calculated. This is the diameter of the sphere.

Use a User-Defined Function

We can create a UDF so that the diameter of the sphere is calculated in a single step.

We use the following steps:

1. Open the VBE and insert a new module.
2. In the new module type in the following code:

3. Select cell B2 and type in the formula =VOLtoDIA(A2) as follows:

4. Press the Enter key and double-click or  drag down the fill handle to copy the formula down the column:

Explanation of the VOLtoDIA User-Defined Function

• Function VOLtoDIA(VOL As Long) As Long. The function is named VOLtoDIA (Volume to Diameter). Its data type is Long. It takes one argument (VOL) of the Long data type.
• VOLtoDIA = ((VOL * 6) / WorksheetFunction.Pi) ^ (1 / 3). The value passed to the function during execution is stored in the VOL variable. It is then multiplied by 6. The product is then divided by the value returned by the in-built Pi worksheet function. The cube root of the result of this division is calculated and assigned to the VOLtoDIA variable.
• The function returns the value in the VOLtoDIA variable as the diameter of the sphere.

Conclusion

In this tutorial we have looked at several approaches we can use to come up with formulas to calculate the diameter of circles and spheres in Excel.

These formulas can be applied in different situations: when the radius of the sphere or circle is known, when the area of the circle is known, when the circumference of the circle or sphere is known, or when the volume of the sphere is known.