In my work on mechanical transmission systems, I have repeatedly encountered the screw gear drive as a compact and reliable solution for large speed reductions and high torque transmission. A screw gear drive, often called a worm drive, consists of a screw gear (the worm) and a mating gear (the worm wheel). Its applications range from hoists and elevators to retractable doors and construction machinery. Because the screw gear drive must operate safely and reliably, a rigorous design calculation is essential. I have found that Microsoft Excel can be transformed into a powerful design tool for screw gear drives, allowing me to automate repetitive calculations, reduce errors, and improve overall efficiency. In this article, I will explain how I structure an Excel workbook to perform the complete design of a screw gear drive, including material selection, contact strength design, bending strength check, thermal balance, and geometric dimension calculations.
The failure modes of a screw gear drive resemble those of ordinary gears, but with important differences. In a closed drive, the worm wheel is typically subject to scuffing and pitting. There is no universally accepted calculation method for scuffing, so I rely on contact fatigue strength as the primary design criterion. For continuous operation, the screw gear drive generates significant heat, making thermal balance a necessary check. Since the worm wheel teeth are relatively weaker than the screw gear threads, I only need to check the worm wheel teeth for bending strength. By organizing all relevant equations and data into an Excel worksheet, I can quickly evaluate whether a proposed screw gear design satisfies strength and thermal requirements.
To give a clear overview of the process, I first developed a design flow chart that links every variable and formula. This flow chart helps me understand the entire screw gear design procedure at a glance and makes it easy to adjust parameters. The flow begins with known conditions such as input power, screw gear speed, output speed, and service life. It proceeds through material selection, allowable stress determination, contact strength design, efficiency verification, bending strength check, thermal balance calculation, and finally geometric dimension calculation. Each step feeds into the next, and the Excel workbook mirrors this structure exactly.
I start by creating a new Excel workbook and naming it as a technical file for screw gear drive design. I set the page layout to A4 size. The first row is merged, filled with a color, and used for the main title. In the rows below, I enter the known conditions. For example, in cells A2 to A6, I input labels such as “Known conditions”, “Screw gear input power P”, “Screw gear speed n1”, “Output wheel speed n2”, and “Service life”. The corresponding values are placed in cells B3 to B6 and filled with light blue to indicate known data. Units are placed in C3 to C6. For service life, I provide options such as “select one of the two” and “single shift”, with the actual hours entered in a yellow cell. This color coding is a simple but effective way to distinguish known inputs, independent variables, and calculated results.
Material selection and heat treatment are critical for the screw gear drive. In my worksheet, I use data validation to create drop-down menus. For the worm wheel material, I list options such as ZCuSn10Pbl, ZCuSn5Pb5Zn5, and ZCuAl9Fe4Ni4. For the screw gear material, I list options such as 20CrMnTi, 20Cr, 45 high-frequency quenched, 40Cr high-frequency quenched, and 45 tempered. The relative sliding speed is also selected from a drop-down menu with options including ≤25 m/s, ≤12 m/s, ≤10 m/s, and ≤2 m/s. Hardness values are selected from another list, for example 56–62 HRC, 40–50 HRC, 50–55 HRC, 45–50 HRC, 50–55 HRC, and 220–250 HBW. These selections automatically guide the allowable stress values.
I then define the basic allowable contact stress for the bronze worm wheel and the basic allowable bending stress for the worm wheel material. I insert comments containing the relevant tables so that I can refer to them without leaving the worksheet. The allowable contact stress is selected from a list such as 180, 200, 220, 110, 135, 125, and 150 MPa. The allowable bending stress is selected from a list such as 46, 58, 66, 32, 41, 112, 40, 73, 83, 51, 140, and 50 MPa. The casting method for the worm wheel is also chosen from a drop-down menu, with options such as sand casting and metal mold casting. These choices reflect the practical manufacturing conditions of the screw gear drive.
Next, I calculate the number of stress cycles and the corresponding life factors. The number of meshing cycles per revolution of the worm wheel is denoted by $j$. The working life in hours is calculated as:
$$L_h = \text{service years} \times 300 \times \text{shift factor}$$
For a single shift, the shift factor is 8 hours per day. The stress cycle number $N$ is then:
$$N = 60 \, n_2 \, j \, L_h$$
I also add a note that when $N = 10^7$, the life factor $K_{HN} = 1$. When $N > 25 \times 10^7$, I set $N = 25 \times 10^7$. When $N < 2.6 \times 10^5$, I set $N = 2.6 \times 10^5$. The life factor for contact stress is:
$$K_{HN} = \left( \frac{10^7}{N} \right)^{1/8}$$
Similarly, for bending stress, the life factor is:
$$K_{FN} = \left( \frac{10^6}{N} \right)^{1/9}$$
I also provide alternative calculations for cases where the stress cycle number falls outside the normal range, with notes that when $N > 25 \times 10^7$, set $N = 25 \times 10^7$, and when $N < 1 \times 10^5$, set $N = 1 \times 10^5$. These conditional formulas ensure that the screw gear drive design remains within valid empirical limits.
The allowable stresses are then computed as:
$$[\sigma_H] = K_{HN} \cdot [\sigma_{0H}]$$
$$[\sigma_F] = K_{FN} \cdot [\sigma_{0F}]$$
In the Excel sheet, I place these formulas in dedicated cells and fill them with purple to indicate calculated values. I also include instructions to select the correct values from the front or rear data depending on whether the life factor was adjusted. This makes the worksheet self-documenting and easy to audit.
For the contact strength design, I begin by selecting the number of screw gear threads $z_1$ and the number of worm wheel teeth $z_2$. The transmission ratio is:
$$i_{12} = \frac{n_1}{n_2}$$
The number of worm wheel teeth is then:
$$z_2 = z_1 \cdot i_{12}$$
I insert a comment with recommended values of $z_1$ and $z_2$ for common transmission ratios. The screw gear torque $T_1$ is calculated from the input power and speed:
$$T_1 = 9550000 \cdot \frac{P}{n_1}$$
The worm wheel torque $T_2$ is:
$$T_2 = T_1 \cdot i_{12} \cdot \eta$$
where $\eta$ is the screw gear transmission efficiency. I insert a comment with a table of typical efficiencies. The contact strength design formula for the screw gear drive is:
$$m^2 d_1 \ge K \cdot T_2 \left( \frac{480}{z_2 [\sigma_H]} \right)^2$$
Here $m$ is the module, $d_1$ is the reference diameter of the screw gear, and $K$ is the load factor, typically taken as 1.1 to 1.4. I use a drop-down menu or a yellow cell for $K$. The calculated value of $m^2 d_1$ is compared with standard combinations from a table of module and diameter values. I then select the standard module $m$ and the corresponding screw gear diameter $d_1$.
Once $m$ and $d_1$ are chosen, I calculate the lead angle of the screw gear:
$$\tan \gamma = \frac{z_1 m}{d_1}$$
In Excel, I compute the angle in degrees using:
$$\gamma = \frac{180}{\pi} \arctan\left( \frac{z_1 m}{d_1} \right)$$
The sliding speed is then:
$$v_s = \frac{\pi d_1 n_1}{60000 \cos \gamma}$$
I compare this sliding speed with the allowable value selected earlier. A logical function in Excel checks whether the speed is suitable. If it is not, the worksheet displays a message asking me to recalculate.
The equivalent friction angle $\rho_v$ for the screw gear drive is selected from a table based on the sliding speed. I convert the selected value into decimal degrees using a formula that extracts degrees and minutes. The meshing efficiency is:
$$\eta_1 = \frac{\tan \gamma}{\tan(\gamma + \rho_v)}$$
I also include an additional efficiency factor $\eta_2 \eta_3$ representing bearing and lubrication losses, typically 0.95 to 0.97. The total efficiency is:
$$\eta = \eta_1 \cdot \eta_2 \eta_3$$
This total efficiency is used to refine the torque calculation and is also used in the thermal balance check.
For the bending strength check of the worm wheel teeth, I select the tooth form factor $Y_{F2}$ from a table based on the equivalent number of teeth. The bending stress is calculated as:
$$\sigma_F = \frac{1.53 K T_2 Y_{F2} \cos \gamma}{d_1 z_2 m^2}$$
I then compare $\sigma_F$ with the allowable bending stress $[\sigma_F]$. A logical function indicates whether the bending strength is sufficient. If not, the design must be revised.
Thermal balance is essential for continuous operation of a screw gear drive. I define the heat transfer coefficient of the housing $k_s$, typically 10 to 17 W/(m²·°C). The ambient air temperature $t_0$ is usually taken as 20°C. The allowable lubricating oil temperature $t_1$ is typically 70 to 90°C. The center distance $a$ is:
$$a = \frac{d_1 + m z_2}{2}$$
The required heat dissipation area is:
$$A \ge \frac{1000 P (1 – \eta)}{k_s (t_1 – t_0)}$$
I also estimate the actual heat dissipation area from the center distance:
$$A_{\text{est}} = 0.33 \left( \frac{a}{100} \right)^{1.75}$$
If the estimated area is less than the required area, the worksheet advises adding cooling fins or other heat dissipation measures. This check ensures that the screw gear drive will not overheat during operation.
Finally, I calculate the geometric dimensions of the screw gear and worm wheel. The addendum coefficient $h_a^*$ is taken as 1, and the clearance coefficient $c^*$ is taken as 0.2, according to national standards. The key dimensions are summarized in the table below.
| Parameter | Symbol | Formula | Unit |
|---|---|---|---|
| Screw gear tip diameter | $d_{a1}$ | $d_1 + 2 h_a^* m$ | mm |
| Screw gear root diameter | $d_{f1}$ | $d_1 – 2 (h_a^* + c^*) m$ | mm |
| Screw gear axial pitch | $p_{a1}$ | $\pi m$ | mm |
| Threaded length | $b_1$ | $(11 + 0.06 z_2) m$ | mm |
| Worm wheel reference diameter | $d_2$ | $m z_2$ | mm |
| Worm wheel tip diameter | $d_{a2}$ | $d_2 + 2 h_a^* m$ | mm |
| Worm wheel root diameter | $d_{f2}$ | $d_2 – 2 (h_a^* + c^*) m$ | mm |
| Worm wheel outer diameter | $d_{e2}$ | $d_{a2} + \frac{6 m}{z_1 + 2}$ | mm |
| Worm wheel width | $b_2$ | $0.75 d_{a1}$ | mm |
I fill all calculated cells with purple and all independent input cells with yellow. This color convention makes the worksheet intuitive. I also add comments to cells where standard tables are needed, such as the table of module and diameter combinations, the table of tooth form factors, and the table of friction angles. By embedding these tables as comments, I keep the main worksheet clean while providing immediate reference.
The design of a screw gear drive involves many interdependent parameters. For example, the sliding speed depends on the lead angle, which depends on the module and diameter; the efficiency depends on the friction angle, which depends on the sliding speed; and the thermal balance depends on the efficiency. Excel handles these circular dependencies well if I organize the calculation sequence properly. I typically use iterative calculation enabled in Excel options to resolve any circular references. Alternatively, I can structure the worksheet so that the friction angle is selected after the sliding speed is computed, and the efficiency is then computed, which feeds back into the torque and contact stress. This sequential approach works well because the friction angle changes slowly with sliding speed.
To illustrate the data validation and drop-down lists, I often prepare a separate hidden sheet containing all the standard tables. For instance, the table of module and diameter combinations might include:
| Module $m$ (mm) | Screw gear diameter $d_1$ (mm) | $m^2 d_1$ (mm³) |
|---|---|---|
| 2 | 18 | 72 |
| 2.5 | 22.4 | 140 |
| 3.15 | 28 | 278 |
| 4 | 35.5 | 568 |
| 5 | 45 | 1125 |
| 6.3 | 56 | 2223 |
| 8 | 71 | 4544 |
| 10 | 90 | 9000 |
| 12.5 | 112 | 17500 |
| 16 | 140 | 35840 |
Similarly, the table of tooth form factors $Y_{F2}$ for the worm wheel might be based on the equivalent number of teeth $z_v = z_2 / \cos^3 \gamma$. A typical table is shown below.
| $z_v$ | $Y_{F2}$ |
|---|---|
| 20 | 2.80 |
| 24 | 2.65 |
| 28 | 2.55 |
| 30 | 2.50 |
| 35 | 2.40 |
| 40 | 2.32 |
| 45 | 2.26 |
| 50 | 2.22 |
| 60 | 2.14 |
| 80 | 2.06 |
| 100 | 2.00 |
The friction angle $\rho_v$ for a screw gear drive depends on the sliding speed and the materials. A typical table for steel screw gear and bronze worm wheel is given below.
| Sliding speed $v_s$ (m/s) | Friction angle $\rho_v$ (degrees and minutes) |
|---|---|
| 0.01 | 5° 45′ |
| 0.05 | 4° 34′ |
| 0.10 | 3° 50′ |
| 0.25 | 3° 05′ |
| 0.50 | 2° 35′ |
| 1.0 | 2° 10′ |
| 2.0 | 1° 45′ |
| 3.0 | 1° 30′ |
| 4.0 | 1° 20′ |
| 5.0 | 1° 10′ |
| 10.0 | 0° 55′ |
| 15.0 | 0° 50′ |
| 25.0 | 0° 45′ |
When I select a friction angle from this table, I enter it as a text string such as “3° 05′”. Excel can then parse the degrees and minutes using the formulas:
$$\rho_v = \text{LEFT}( \text{cell}, 1 ) + \frac{\text{MID}( \text{cell}, 2, 2 )}{60}$$
This gives the friction angle in decimal degrees. I then use it in the efficiency formula. This approach avoids manual conversion and reduces errors.
The thermal balance calculation is equally important for screw gear drives that operate continuously. I define the heat transfer coefficient $k_s$ and the temperatures. The required area formula uses the input power and total efficiency. I also estimate the housing area from the center distance. If the estimated area is insufficient, I can either increase the center distance, add cooling fins, or use forced cooling. The Excel worksheet flags this condition with a logical function. This makes the screw gear drive design not only strong enough but also thermally safe.
One of the main advantages of using Excel for screw gear drive design is that I can easily perform parameter studies. For example, I can change the number of screw gear threads $z_1$ and immediately see the effect on the transmission ratio, lead angle, sliding speed, efficiency, and contact stress. I can also vary the material selection and see how the allowable stresses change. This interactive capability is invaluable for optimizing a screw gear drive before committing to manufacturing. I often create a summary table that lists the final design parameters and the pass/fail status of each check.
| Design Check | Calculated Value | Allowable Value | Status |
|---|---|---|---|
| Contact stress $[\sigma_H]$ | Calculated in worksheet | From material table | Pass if calculated ≤ allowable |
| Bending stress $\sigma_F$ | Calculated in worksheet | $[\sigma_F]$ from material table | Pass if calculated ≤ allowable |
| Sliding speed $v_s$ | Calculated in worksheet | Selected from drop-down | Pass if calculated ≤ selected |
| Thermal area $A$ | Calculated in worksheet | Estimated area $A_{\text{est}}$ | Pass if estimated ≥ required |
I also include a geometric dimension summary table in the worksheet. This table lists all the final dimensions of the screw gear and worm wheel, including tip diameters, root diameters, pitch, threaded length, and worm wheel width. These dimensions are directly used for creating manufacturing drawings. Because the formulas are linked to the design inputs, any change in module or number of teeth automatically updates all dimensions. This eliminates the tedious and error-prone manual recalculation that traditional design methods require.
The use of data validation and conditional formatting further enhances the usability of the screw gear design workbook. For example, I can set a rule that if the calculated contact stress exceeds the allowable stress, the cell turns red. This immediate visual feedback allows me to adjust parameters quickly. Similarly, if the thermal area is insufficient, a message appears in a green cell. I find that these features make the workbook accessible even to users who are not experts in Excel formulas.
In my experience, the most common challenge in screw gear design is the interdependence of efficiency and torque. The efficiency depends on the lead angle and friction angle, but the friction angle depends on the sliding speed, which in turn depends on the screw gear diameter and speed. To resolve this, I usually start with an initial estimate of efficiency, calculate the torques and contact stress, select the module and diameter, then compute the sliding speed and friction angle, and finally update the efficiency. One or two iterations are usually sufficient. Excel’s iterative calculation feature can automate this process. I enable it by going to File > Options > Formulas and checking “Enable iterative calculation” with a maximum of 100 iterations and a maximum change of 0.001. This allows the worksheet to converge to a consistent solution.
Another important aspect is the selection of the number of screw gear threads. A single-thread screw gear provides a high transmission ratio but lower efficiency. A multi-thread screw gear increases efficiency but reduces the transmission ratio. The choice depends on the application. For a hoist, a single-thread screw gear may be preferred for its self-locking property. For a power transmission drive, a double-thread or triple-thread screw gear may be better. In my Excel workbook, I use a drop-down menu for $z_1$ with options 1, 2, 4, and 6. The recommended values for $z_2$ are then calculated based on the desired transmission ratio. I also insert a comment that provides guidance on selecting $z_1$ and $z_2$.
The material selection for the screw gear and worm wheel is also critical. The screw gear is usually made of hardened steel, while the worm wheel is made of bronze or cast iron. The combination of a hardened steel screw gear and a bronze worm wheel provides good wear resistance and low friction. In my workbook, I use drop-down menus for both materials. When the user selects a material, the corresponding allowable stresses are automatically updated through lookup functions. This ensures that the design is consistent with the selected materials.
The contact strength design formula for the screw gear drive uses the allowable contact stress $[\sigma_H]$. This stress is derived from the basic allowable contact stress $[\sigma_{0H}]$ multiplied by the life factor $K_{HN}$. The basic allowable contact stress depends on the worm wheel material and casting method. For example, for sand-cast ZCuSn10Pbl, the basic allowable contact stress is 180 MPa. For metal-mold-cast ZCuSn10Pbl, it is 220 MPa. These values are stored in a table and retrieved based on the user’s selections. The life factor $K_{HN}$ depends on the number of stress cycles $N$. The formula is:
$$K_{HN} = \left( \frac{10^7}{N} \right)^{1/8}$$
This formula is valid for $N$ between $2.6 \times 10^5$ and $25 \times 10^7$. Outside this range, the limits are applied. The same logic applies to the bending stress life factor $K_{FN}$, but with an exponent of 1/9 and a reference of $10^6$. These formulas are standard in mechanical design and are easily implemented in Excel.
The bending strength check for the worm wheel teeth uses the tooth form factor $Y_{F2}$. This factor depends on the equivalent number of teeth $z_v$, which is calculated as:
$$z_v = \frac{z_2}{\cos^3 \gamma}$$
The tooth form factor is then looked up from a table. The bending stress formula is:
$$\sigma_F = \frac{1.53 K T_2 Y_{F2} \cos \gamma}{d_1 z_2 m^2}$$
If the calculated bending stress is less than the allowable bending stress, the design passes. Otherwise, the module or diameter must be increased. The Excel worksheet automatically performs this check and displays the result.
The thermal balance calculation ensures that the screw gear drive does not overheat. The required heat dissipation area is:
$$A \ge \frac{1000 P (1 – \eta)}{k_s (t_1 – t_0)}$$
The estimated heat dissipation area of the housing is:
$$A_{\text{est}} = 0.33 \left( \frac{a}{100} \right)^{1.75}$$
If $A_{\text{est}} < A$, the worksheet advises adding cooling fins. This calculation is straightforward but often neglected in manual design. By including it in the Excel workbook, I ensure that the screw gear drive is thermally safe.
Finally, the geometric dimensions of the screw gear and worm wheel are calculated using the formulas listed in the table above. These dimensions are essential for manufacturing. In the Excel workbook, I place the formulas in cells and format them to display two decimal places. I also include a check for the worm wheel width, which should be at least $0.75 d_{a1}$. The final dimensions are then copied into a summary table for easy reference.
In conclusion, I have found that Excel is an excellent tool for designing screw gear drives. By organizing the design process into a structured worksheet, I can perform all necessary calculations quickly and reliably. The use of data validation, drop-down menus, conditional formatting, and iterative calculation makes the worksheet user-friendly and robust. The design flow chart helps me understand the sequence of calculations, and the tables of standard values ensure that the design adheres to established standards. I can easily adjust parameters and immediately see the effect on the screw gear drive performance. This approach improves both the efficiency and the reliability of screw gear drive design. I recommend it to any engineer involved in mechanical transmission design.
The screw gear drive remains a vital component in many machines, and its proper design is essential for safe operation. By leveraging Excel, I can focus on the engineering decisions rather than the repetitive arithmetic. The worksheet can be reused for different screw gear drive applications by simply changing the input values and selecting new materials. This “develop once, use many times” approach saves time and reduces the risk of calculation errors. I continue to refine my Excel workbook as I encounter new materials and new design requirements. The flexibility of Excel makes it an ideal platform for screw gear drive design and analysis.