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:

- The curve passes exactly through both points
- The slope of the curve at the end points is equal to the slope of the adjacent segments
- 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.

### Like this:

Like Loading...

*Related*

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

LikeLike

Thanks for providing this. I am going to be trying it out.

LikeLike

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

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

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

can someone explain me how to use it??

LikeLike

Peter – I have just posted something about how to use array formulas with the CSplineA function as an example.

http://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/

If you still have any questions after reading that, could you be more specific about what your problems are.

LikeLike

answer?

LikeLike

question?

LikeLike

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

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

LikeLike

“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

LikeLike

Pingback: useful links | IL SUPEREROE

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

LikeLike

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.

LikeLike

Ah, figured as much. Thanks for the quick response. I’ll let you know if I find a workaround.

LikeLike

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!

LikeLike

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.

LikeLike

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.

LikeLike

Jim your code will create an empty array called Xint. I have modified the code to create an array XintTemp(1,1), copy Xint into that, then copy XintTemp into Xint. Also the value nint needs to be set to 1.

I have only done quick testing, but it seems to be working OK.

Download from:

http://interactiveds.com.au/software/CSpline2.zip

LikeLike

Thanks for the quick reply.

LikeLike

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

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

Pingback: Excel相关 | 优的水杯