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

Csplinea Function

Example 1; Fit spline to 5 data points

Example 1; Fit spline to 5 data points

 

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

 

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 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

 

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

Polynomial coefficients from example 3

 

Example 3; Bending Moments

Example 3; Bending Moments

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

About these ads
This entry was posted in Beam Bending, Excel, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

27 Responses to Cubic Splines

  1. Raj says:

    Thank you for this spreadsheet/macro. Incredibly useful and very quick!

    Like

  2. Pingback: Continuous beam analysis with cubic splines « Newton Excel Bach, not (just) an Excel Blog

  3. Pingback: Calcolare lo spread emittente - Pagina 2 - I Forum di Investireoggi

  4. Pingback: 2010 in review | Newton Excel Bach, not (just) an Excel Blog

  5. Peter says:

    can someone explain me how to use it??

    Like

  6. Berthold says:

    answer?

    Like

  7. Pingback: Daily Download 22: Splines and Curves | Newton Excel Bach, not (just) an Excel Blog

  8. totosugito says:

    Hi, Thank you for your demo and information about this spline method. I have convert this csplineA excel file to C#. Please check at my site :)
    Thank you

    Like

  9. “Cubic Splines | Newton Excel Bach, not (just) an Excel
    Blog” ended up being a superb blog post, can not wait to browse far more of ur postings.

    Time to waste numerous time on the internet haha.
    Thanks a lot ,Preston

    Like

  10. Pingback: useful links | IL SUPEREROE

  11. NE says:

    This looks like a very useful function, but when I open it in Excel:Mac 2008 there is an error in the spline results. Somewhere along the line, Excel thinks something is text when it should be a number (#NAME?). Any idea on how to solve this problem for Macs? Thanks for your work on this, hope I can use it. Cheers

    Like

    • dougaj4 says:

      Unfortunately Excel for Mac 2008 does not have VBA, so no User Defined Function will work in that version. I believe that the latest Excel for Mac does have VBA restored, so UDFs should work if you update, but I can’t guarantee it as I don’t have a Mac available for testing.

      Like

  12. purpledelight says:

    This is a great macro, thanks very much for sharing. I’m fairly new to spline interpolation so apologies if my question is obvious but is this a basis-spline? I read that a basis-spline would only work on ascending values of x, order, but this macro works for non-ascending values of x. However, if this isn’t a basis-spline could you please briefly explain what type of spline you would categorise it as? Thank you!

    Like

    • dougaj4 says:

      The x values do need to be in ascending order! The function will return a result with non-ascending x values (as long as no two adjacent values are equal), but the resulting curve makes no sense.

      For a curve where the x values may not be ascending the most common option used is a Bezier curve. There may be others, but I haven’t looked into it.

      Like

  13. Jim says:

    Thanks for posting this very useful code. One nit: you might mention that the interpolation x-values (Xint in the VBA code) needs to have at least three values in its range to get proper results. I tried it for a single-cell Xint, which caused Xint to be passed in as a double. This flagged an error on the call to UBound(Xint) since that function works only on arrays. To get around that, I inserted:

    If Not Typename(Xint) Like “*()” Then
    ReDim Xint(1, 1)
    End If

    CSplineA returned results after this change, but then I found that calls with single cells for Xint returned grossly incorrect values. When I changed Xint to also include the XVal entries that bracket my desired interpolation point, however, the problem was fixed.

    Like

  14. dougaj4 says:

    Thanks Jim, I’ll have a look at that.

    Like

  15. Praba Veera says:

    Hi sir. Currently I’m working on a project titled interpolation of planar curve with different parameterization and my supervisor told me to create a smooth B-spline curve using Microsoft Excel. I am having difficulties with the task given and may i know if the example you have given above could be used to create the b-spline? Do you have any example for the B-spline Curve? Thank you in advance.

    Like

    • dougaj4 says:

      The simplest way to create a a smooth B-spline in Excel is to create an XY (scatter) chart from a set of points, and select the smoothed line option to connect the points. The resulting curve is an example of a B-spline.

      The cubic splines described here are also B-splines, so you could use example from here as well.

      Like

      • Praba Veera says:

        Thanks for the reply sir. Now, after doing the B-spline Basis function calculations for zeroth and 1st degree, how am i supposed to link the calculation to create a B-spline curve with control points using Excel and Could you please enlighten me on the B-spline basis function calculations for 2nd degree and onwards ? I’m sorry for troubling you sir because my supervisor not providing me enough informations. Thanks in advance.

        Like

  16. Pingback: Excel相关 | 优的水杯

  17. Markus says:

    Thanks for sharing, you made my day!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s