Numerical Differentiation in Excel: Calculating First Derivatives

Numerical methods are powerful tools for mechanical engineering design calculations. In this post, I will demonstrate how to calculate the first derivative (velocity) of a motion profile using numerical methods directly in Microsoft Excel, without the need for specialized math software.

While textbooks provide extensive derivations, for practical engineering, we can jump straight to the high-accuracy finite difference formulas.

The 5-Point Stencil Formula

To calculate the first derivative of a function f(x) with high precision, we use the "Five-Point Stencil" method. This formula utilizes interior points to achieve an error order of h⁴, which is significantly more accurate than standard forward or backward difference methods.

f'(x_i) = [ -f(x_i+2) + 8f(x_i+1) - 8f(x_i-1) + f(x_i-2) ] / 12h

Where:
i = The current point of interest
h = The step size (the distance between points)
The smaller the step size (h), the higher the accuracy of the result.

Advertisement

Example Application: Cycloid Cam Motion

To demonstrate this, let's use a favorite curve in machine design: the Cycloid. This curve provides smooth motion by starting with zero acceleration, accelerating to peak, and decelerating back to zero.

The displacement equation for a Cycloid is:

s = H_m × [ (θ / β) - (1 / 2π) × sin( 2π × θ / β ) ]

Where H_m is total stroke, θ is current angle, and β is total angle of motion.

Figure 1: The smooth displacement curve of Cycloid motion.

Analytically, the velocity (first derivative) is calculated as:

v = (H_m / β) × [ 1 - cos( 2π × θ / β ) ] × ω

However, instead of using this analytical formula, we will use the Numerical Method (Formula 1) to derive the velocity from the displacement data alone.

Step 1: Create the Data Table

First, create an Excel table with the displacement data. In this example, we use a step size of 1.25 degrees.

Angle (deg)	Angle (rad)	Displacement s (mm)
0.00	0.0000	0.00000
1.25	0.0218	0.00346
2.50	0.0436	0.02758
3.75	0.0654	0.09270
5.00	0.0873	0.21894
...	...	...

Note on Step Size (h): Although the table shows degrees, the numerical calculation requires radians.
h = 1.25 × π / 180 = 0.021816616 rad

Advertisement

Step 2: Calculate the Derivative

Let's calculate the velocity at 15 degrees.
Assume Camshaft Speed = 50 RPM.
Angular velocity (ω) = 2 × π × 50 / 60 = 5.2359 rad/s

Using the 5-point stencil formula, we reference the two cells above and the two cells below our target point (15 deg).

Figure 2: Visualizing the 5-point selection in Excel. Points x_i-2 to x_i+2 are used.

Calculation Example:

f'(15°) = [ -4.20 + (8 × 3.48) - (8 × 2.23) + 1.72 ] / (12 × 0.0218) × 5.236

Result = 149.23 mm/s (0.149 m/s)

By dragging this formula down in Excel, you instantly generate the complete velocity profile without complex calculus.

Figure 3: The resulting velocity profile matches the analytical solution perfectly.

This technique proves that you don't always need MATLAB or Mathematica. Excel, combined with a basic understanding of numerical methods, is often sufficient for high-precision engineering design.