Cubic Splines

Cubic splines are used to fit a smooth curve to a series of points with a piecewise series of cubic polynomial curves.  In addition to their use in interpolation, they are of particular interest to engineers because the spline is defined as the shape that a thin flexible beam (of constant flexural stiffness) would take up if it was constrained to pass through the defined points.  This post will present an Excel User Defined Function (UDF) to generate a “natural” cubic spline for any series of 3 or more points.  Later posts will look at alternative spline formulations, and applications of the cubic spline to structural analysis.

A cubic spline is defined as the curve that for any two adjacent internal points:

1. The curve passes exactly through both points
2. The slope of the curve at the end points is equal to the slope of the adjacent segments
3. The curvature of the curve at the end points is equal to the curvature of the adjacent segments

Alternative provisions for the end segments will generate different spline curves over the full extent of the curve.  The most common provision for the ends is that the curvature is zero at both ends.  This is known as a “natural cubic spline”.  In a structural analysis context this corresponds to a beam that is free to rotate at both ends, but is constrained in position at the ends and a number of internal points.

Further details of the theory of cubicl splines, and an algorithm for generating natural cubic splines are given in this Wikipedia article.

An excel spreadsheet with a UDF for generating cubic splines, based on the algorithm in the Wikipedia article, can be downloaded from: CSplineA.zip

The download is open source, and full VBA code for the UDF is freely accessible.

Example screen shots from this file are shown below:

Csplinea Function

Example 1; Fit spline to 5 data points

 

Example 1; Fit spline to 5 data points

 

Example 2; Fit spline to 9 data points on a circular arc

 

Example 2; Fit spline to 9 data points on a circular arc

 ”Dummy” data points at each end allow the curvature at the start and end points to be adjusted to the required value.

Example 2; Fit spline to 9 data points on a circular arc

 

Example 3; Fit spline to the deflected shape of a 3 span beam

 

Example 3; Fit spline to the deflected shape of a 3 span beam

 

Polynomial coefficients from example 3

 

Example 3; Bending Moments

Bending moments are calculated by multiplying the curvature at each point by the beam flexural stiffness, EI.