## 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: CSpline2.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.

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

### 35 Responses to Cubic Splines

1. Raj says:

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

Like

• John says:

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

Like

2. Peter says:

can someone explain me how to use it??

Like

3. Berthold says:

Like

• dougaj4 says:

question?

Like

4. totosugito says:

Thank you

Like

5. “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

6. Pingback: useful links | IL SUPEREROE

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

• NE says:

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

Like

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

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

• dougaj4 says:

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.

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

Like

• Jim says:

Like

10. dougaj4 says:

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

Like

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

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

13. Markus says:

Thanks for sharing, you made my day!

Like

14. Andrew says:

Is there a spline that can be used to interpolate using polar coordinates? I have two irregular (roundish) closed shapes that I want to work out the best way to fit them together, i.e. rotate one relative to the other to give the least amount of difference at the seam where they touch.

In addition to rotating, I’d like to be able to change the origin of one shape w.r.t. the other, then use the spline interpolation to calculate the new coordinates to make the comparison. Any advice on how to do the spline in polar coordinates would be appreciated. Thanks in advance.

Liked by 1 person

• dougaj4 says:

Andrew – The latest version of the CSpline spreadsheet has Cardinal Spline function that should do what you want.

Like

• Andrew says:

Doug, I’ve had a look at the card spline, and I must admit that I am a little bit uncertain as to what the three parameters are…
If I have polar coordinates after I transform my origin position, how do I now interpolate my new polar coordinates, i.e. after my translation of the origin, if my original point was 0 degrees, 10 radius, 10 degrees 10 radius etc, and is now -1 degrees 9.8 radius and 8 degrees 10.1 radius, how do I calculate my new 0 degrees and 10 degrees radius values?
Sorry for the n00b question.
Regards
Andrew

Like

• excel1star says:

Hi. I have followed your question with some interest. Send me an email
alfred.vachris@gmail.com . I would like to propose an alternate solution.
Would love to see a sketch of your two curves.
Best Regards
Alfred Vachris Excel VBA Developer

Like

15. dougaj4 says:

Andrew – it’s not an easy question, a search on “cubic splines polar coordinates” comes up with quite a few hits but they are all pretty heavy on the maths, and I don’t have time to work my way through them at the moment.

With the cardinal spline in the CSpline2 spreadsheet the XY values are the usual Cartesian coordinates, and the L value is a measure of the distance measured along the curve. The distance between each point defining the spline is given a value of 1, so an L value of 1.5 defines a point half way along the segment from point 2 to point 3. It seems that the output starts at L=1, i.e. from point 2 onwards (it is some time since I wrote the code, and I don’t remember the reason for that).

Some quick tests with the CardSpline function suggest that it doesn’t work well with a closed loop. You might do better with the xl_PSplinep function in xl_Spline-Matrix2. To use that you will need to download and install Python and the Alglib library as described in the link below (both are free):(http://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/).
To use this one it seems to work best if you convert the points defining your curves to XY coordinates, generate XY coordinates for the complete loop at fairly close spacing, and then convert these back to polar. Finally you could use an ordinary cubic spline to interpolate between the polar coordinates, to get the radius for any given angle. Note that in this function a value between 0 and 1 defines a point along the complete curve.

If you get a working system out of that (or find a simpler way), I would be interested to hear.
Don’t hesitate to ask further questions.

Liked by 1 person

• lori says:

Hi Doug – it shouldn’t be hard to modify the spline end conditions for a closed curve by continuing the cubic formula around the loop.

In the case of a cardinal spline connecting ABCD, just apply the same algorithm to each of the four middle segments of DABC,ABCD,BCDA,CDAB. (i believe a similar approach is used for smooth scaling of fonts when zooming in on text on a touchscreen.)

For a curve with second order continuity the end conditions can be modified as in: http://mathworld.wolfram.com/CubicSpline.html [eqn (19)]

Numerous references suggest other choices of basis functions may be better suited to angular data (RBF, trigonometric, wavelets, Euler spiral, …) but i haven’t any experience of these in practice. The scipy python library has some options for generating closed curves that may be worth exploring too.

Liked by 1 person

16. dougaj4 says:

Thanks for your comments Lori and Alfred. I’ll be travelling (Italy and UK) for the next four weeks, so I won’t have much time to contribute, but I will follow any developments with interest.

Like

17. Natal says: