Skip to main content
Suomeksi
TAMK Student’s Guide
Log in to the intranet

Tampere University Student’s Guide

Din 5480 Spline — Calculator Excel

Comprehensive Guide to Building a DIN 5480 Spline Calculator in Excel

The DIN 5480 standard is a widely used German specification for involute splines based on reference diameters. For mechanical engineers and designers, having a functional DIN 5480 spline calculator in Excel is essential for quickly determining gear geometry, tolerances, and inspection dimensions without manual lookup tables.

This article provides the formulas and structural logic required to build a professional-grade calculation spreadsheet. 1. Understanding DIN 5480 Parameters

The DIN 5480 standard uses a specific nomenclature. A typical designation like DIN 5480 - W 120 x 3 x 38 x 8f provides the following inputs: W/N: "Welle" (Shaft/External) or "Nabe" (Hub/Internal). 120: Reference diameter ( 3: Module ( ), which defines the tooth size. 38: Number of teeth ( 8f: Tolerance class (8) and deviation series (f). 2. Fundamental Geometry Formulas for Excel

To build your calculator, input the primary variables (Module , and Pressure Angle ) into designated cells and use these formulas: Excel Formula Logic Reference Diameter Base Circle Diameter Pitch Shaft Tip Diameter da1d sub a 1 end-sub Shaft Root Diameter df1d sub f 1 end-sub Total Tooth Depth Note: The addendum modification factor (

) is often required to adjust the reference diameter to fit standard bearing sizes. 3. Advanced Inspection Dimensions

A robust Excel tool must calculate "over pins" or "between pins" dimensions for quality control. Measurement Over Pins (External Spline) The dimension over pins ( Mkcap M sub k

) is critical for verifying tooth thickness. It requires calculating the involute function ( ) for the pressure angle at the pin center. Pin Diameter ( Dpcap D sub p ): Typically

Formula: Requires iterative solving or complex trigonometry based on the effective tooth thickness ( 4. Handling Tolerances and Fits DIN 5480 defines various fits through letter codes:

Slip Fits: Series "a" through "g" (External) or "F" and "G" (Internal). Line-on-Line Fits: "h" (External) or "H" (Internal). Interference Fits: "j" through "v".

In Excel, you can use VLOOKUP or INDEX/MATCH functions to pull deviation values ( Asecap A sub s e end-sub Aiecap A sub i e end-sub

) from a digitized version of the DIN 5480-14 tolerance tables. 5. Benefits of Using a Custom Excel Calculator Spline Calculator - Ondrives Precision Gears

While there isn't a single official "paper" published by DIN as an Excel file, you can find several highly effective Excel-based calculators and guides that implement the DIN 5480 standard for involute splines. Recommended Excel Resources

Hayes Broaching Service Spreadsheet: This Spline Data Spreadsheet provides a comprehensive list of spline designations, including DIN 5480 (formatted as W x Module x Pressure Angle x Teeth x Class). It includes parameters for module, number of teeth, and pressure angle .

Gerhardt Gear Spline Program: This Excel-based Spline Program allows you to enter specific parameters (number of teeth, pitch, pressure angle) to calculate dimensions for various standards, including those compatible with DIN profiles .

Scribd DIN 5480 Calculator Guide: For those building their own Excel sheet, this Calculator Guide provides the specific Excel formulas needed to calculate diameters, tooth depth, and stresses . Key Calculation Formulas for Excel din 5480 spline calculator excel

If you are setting up your own spreadsheet, use these standard formulas for a 30∘30 raised to the composed with power pressure angle ( Excel Formula Logic Description Base Circle Diameter ( ) =Reference_Diameter * COS(RADIANS(30)) Fundamental for involute geometry. Tip Diameter ( ) =Reference_Diameter + (2 * Module) Outer boundary of the teeth. Root Diameter ( ) =Reference_Diameter - (2.5 * Module) Bottom of the tooth space. Total Tooth Depth ( ) =2.25 * Module Combined height of the tooth. Circular Pitch ( ) =PI() * Module Distance between corresponding points on adjacent teeth. Technical Considerations

Reference Diameter: Unlike ANSI standards, DIN 5480 uses a reference diameter that is often made equal to a standard bearing bore for easy slip-fitting .

Designation Decoding: A standard designation like DIN 5480 - W 50 x 2 x 24 x 8h indicates: W: Shaft (External spline) / N: Hub (Internal spline). 50: Reference Diameter. 2: Module (size of the tooth). 24: Number of teeth. 8h: Tolerance Class and Deviation . If you'd like, I can help you:

Write the specific Excel logic for a particular cell (like pin measurements).

Explain the difference between "Actual" vs "Effective" tooth thickness.

Find specific values for a given DIN 5480 designation from a table. Let me know how you'd like to refine your calculation tool. involute splines - Hayes Broaching Services

A DIN 5480 spline calculator in Excel is a vital tool for mechanical engineers needing to calculate dimensions for involute splines used in heavy-duty torque transmission . While professional software like eAssistant

offers more advanced features, Excel-based calculators provide a cost-effective and highly customizable alternative for standard geometric verification. www.kisssoft.cz Core Functionality of DIN 5480 Excel Calculators The standard DIN 5480 is based on reference diameters

independent of the module, which is unique compared to ANSI or ISO standards. A high-quality Excel tool should automate the following: Geometric Input & Profile Shift

: It must handle profile shifts (addendum modifications) necessary to reach standardized reference diameters. Standardized Variables

: Automated lookups for modules (typically 0.5 to 10), number of teeth (6 to 82), and the uniform 30° pressure angle. Fit & Tolerance Systems

: Calculation of tooth thickness allowances and space width tolerances for various fit types (sliding, interference, or permanent). Inspection Dimensions

: Generation of "over-pin" or "between-pin" dimensions and base tangent lengths for quality control. Top Tools & Resources

For those seeking pre-built templates or robust calculation guides: Ondrives Precision Gears : Provides an online spline calculator

that serves as an excellent reference for the data structure you should replicate in Excel. Scribd Engineering Guides : You can find detailed DIN 5480 Spline Calculation Guides Comprehensive Guide to Building a DIN 5480 Spline

that provide the exact formulas and variable tables needed to build your own spreadsheet. FRENCO GmbH : While primarily professional software, their documentation on spline standards

provides the mathematical basis for fillet radii and tooth shape modifications. Ondrives Precision Gears Critical Review: Pros vs. Cons Spline connections - KISSsoft

The DIN 5480 spline calculator in Excel is a specialized tool used to determine the geometry, tolerances, and inspection dimensions of involute splined connections. These calculators automate the complex formulas of the DIN 5480 standard, which uses a 30° pressure angle and is based on a reference diameter ( dBd sub cap B ) rather than the module alone. 1. Identify Input Parameters

The foundation of a DIN 5480 calculator requires five primary inputs: Module ( ): The size of the tooth. Number of Teeth ( ): Total count of spline teeth. Reference Diameter ( dBd sub cap B

): The governing diameter for the connection (e.g., "120" in W 120 x 3...). Tolerance Class: Typically ranges from 5 to 12 (e.g., 8).

Deviation Series: Denoted by a letter (e.g., "f" for shafts, "H" for hubs). 2. Calculate Reference Geometry

Using the inputs, Excel formulas can derive the standard pitch and base circles: Pitch Diameter ( ): d=m⋅zd equals m center dot z Base Diameter ( ):

db=d⋅cos(30∘)d sub b equals d center dot cosine open paren 30 raised to the composed with power close paren Profile Shift (

): Calculated to align the spline with the chosen reference diameter. The shift factor ( ) must stay between -0.05negative 0.05 and +0.45positive 0.45 . 3. Determine Diameters and Tolerances

The calculator must output critical physical dimensions for both the shaft (W) and hub (N): Tolerances in DIN 5480 splines | GrabCAD Tutorials

2. Excel Calculator – What It Should Do

A useful calculator must:

  • Accept module, teeth, pressure angle, fit class.
  • Compute reference, base, tip, root diameters.
  • Calculate span measurement over pins or ball diameter for inspection.
  • Compute chordal tooth thickness.
  • Apply DIN 5480 tolerances (e.g., +/− based on fit class).

1. Understanding DIN 5480 Basics

DIN 5480 describes involute splines with a 30° or 37.5° pressure angle, module series, and side fit.
Key parameters:

  • Module (m) – 0.5 … 10 mm
  • Number of teeth (z)
  • Reference diameter ( d = m \cdot z )
  • Pressure angle (α) – typically 30° (37.5° less common)
  • Fit type – centered on flanks (no centering diameter)
  • Basic rack profile – similar to ISO 4156

Major dimensions for external spline (shaft):

| Dimension | Formula | |-----------|---------| | Pitch diameter | ( d = m \cdot z ) | | Base diameter | ( d_b = m \cdot z \cdot \cos \alpha ) | | Tip diameter (max) | ( d_a = m \cdot (z+2) ) | | Root diameter (min) | ( d_f = m \cdot (z-2.5) ) |

For internal spline (hub):

  • Tip diameter (min) = ( m \cdot (z+2) )
  • Root diameter (max) = ( m \cdot (z-2.5) )

Tooth thickness / space width are defined via basic rack shift and tolerances (e.g., e‑a, f‑h).

Section 3: Anatomy of a DIN 5480 Spline Calculator in Excel

A complete calculator should include at least four main sections:

❌ Common issues in Excel DIN 5480 calculators

| Issue | Risk | |-------|------| | Missing profile shift correction | Causes wrong tooth thickness | | No difference between generating and non‑generating root | Overestimates strength | | Incorrect tolerance lookup (e.g., treating 7H as a simple +offset) | Non‑conforming spline | | Forgetting that DIN 5480 uses reference diameter not pitch diameter like ISO 4156 | Wrong calculations | | No validation for odd number of teeth | Measurement over pins may be wrong |

Step 2: Calculate Base Dimensions

B5: =B1*B2  (Pitch diameter)
B6: =B5*COS(RADIANS(B3))  (Base diameter)
B7:=B1*(B2+1)-0.2*(B1)  (Approx major diameter shaft)

✅ Pros

  1. No special software required
    Runs in standard Excel (no macros often, just formulas). Accessible on almost any PC.

  2. Fast parametric input
    Enter module, number of teeth, fit class (e.g., H/h, H/k), and reference diameter – get pitch diameter, root diameter, tip diameter, and space/land widths instantly.

  3. Built-in tables
    Good versions include embedded DIN 5480-1:2006 reference tables (profile shift coefficients, tolerances for centering diameters).

  4. Useful for inspection setup
    Calculates important values like base tangent length (Wk) and pin/ball measurement over/under wires, which are essential for checking actual splines with micrometers.

  5. Cost-effective
    Often free or very cheap (€5–€30) compared to CAD add-ons or full gear suites (e.g., Mitcalc, GearTrax).

Summary

This article explains DIN 5480 splines, gives the formulas needed to calculate key dimensions, and provides an easy-to-use Excel implementation (with formulas and a downloadable sample layout). Use it to size external/internal splines, compute fit tolerances, and produce drawing values.

Step-by-Step: Using an Excel DIN 5480 Calculator

Let’s walk through a typical use case: You need to design an external shaft spline with mating internal hub.

Step 1: Input Basic Data

  • Module (m) = 2
  • Number of teeth (z) = 24
  • Tolerance class: External = h (e.g., 24z x 2m x 30° DIN 5480 h)
  • Pin diameter (select from standard table, e.g., 3.5 mm balls)

Step 2: Review Calculated Base Parameters The Excel sheet instantly returns:

  • Reference diameter (dref) = 48.000 mm
  • Base diameter (db) = 41.569 mm
  • Minimum actual tooth thickness (smin) per DIN 5480 table

Step 3: Get M-dm for Machining The calculator solves the involute function and returns:

  • Measurement over 3.5mm pins = 51.934 mm (max), 51.882 mm (min)

Step 4: Generate Inspection Report Print the Excel sheet as a PDF. This becomes your QC document. Save it alongside your CAD model.

Step 5: Export Data (Optional) Some advanced Excel calculators have a button: "Copy to CNC" – sending the variables to a text file. Accept module, teeth, pressure angle, fit class