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

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

https://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相关 | 优的水杯

Thanks for sharing, you made my day!

LikeLike

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.

LikeLiked by 1 person

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

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

I have also edited the link in the post to download the current version.

LikeLike

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

LikeLike

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

LikeLike

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):(https://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.

LikeLiked by 1 person

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.

LikeLiked by 1 person

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.

LikeLike

Super helpful tool. Thank you!

LikeLike

Thank you for providing this tool. I am finding it useful for fitting diffusion data to determine concentration-dependent interdiffusion coefficients.

If I had a dataset with a larger number of XY values, could a function be devised to perform a ‘best fit’ cubic spline scheme featuring a user-prescribed or algorithmically-determined number of splines where the function would pick the optimal endpoints for each spline automatically? I’d hate to work with 100 polynomials for 101 XY points if there is little added benefit as compared to, say, 5 polynomials if the endpoints were well-chosen.

LikeLike

David – yes, there are a few options:

Probably the simplest is the AL_Spline-Matrix spreadsheet from:

https://newtonexcelbach.wordpress.com/2010/06/07/alglib-spline-functions/

This uses a VBA version of the Alglib maths library, which includes spline fitting functions that allow you to specify the number of splines. The download file includes all the necessary routines, so it should run without installing anything else. The only drawback is that the VBA version of Alglib is no longer being updated.

I also have a version that uses the Alglib Python interface, which you can download from:

https://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/

For this one you need Python, Alglib Python version, and ExcelPython. You need to install Python first, then Alglib. The required ExcelPython files are included in the download. See the link for more details.

Finally there is a spreadsheet that links to the Python Scipy package, via ExcelPython. If you install a version of Python that includes Scipy and Numpy (such as Anaconda), then the remaining required files are included in the download from:

https://newtonexcelbach.wordpress.com/2014/12/14/numpy-and-scipy-for-excel/

The best function for your use is probably xl_UniSpline on the Interp sheet, which has an optional K argument, specifying the degree of the spline, and S (between 0 and 1) which controls the degree of smoothing, between none at S = 0 to 1 spline at S = 1. The documentation on the spreadsheet is very sparse, but Scipy comes with a detailed (but not particularly friendly) manual.

Please ask if anything isn’t clear, and let me know if you have any problems getting anything to work.

LikeLike

I am neither au fait enough with the maths nor with the coding for the cspline UDF, but I was delighted a year ago now when I found it browsing the web. I have used it extensively to help me in my online hobby of virtual sailboat racing. Thank you!

Using it also made me teach myself the basics of Excel array algebra, and, because both its source data and results must be set up in columns, a bit more than the basics with respect to indirect addressing (to auto-invert rows of data into columns).

Much more recently I found much the same in terms of functionality and underlying maths from SRS1; their data curve fitter. It is a little easier to use in that it doesn’t need array definitions and is indifferent as to whether the source data is presented in columns or rows. Problem is of course 1/ it is only free to use for a while and 2/ it invents extraordinary tails for points that fall outside the range of the source data.

My question then: are there any plans to make cspline work with data in rows as well as with data in columns?

LikeLike

Jan – I’m glad you find it useful.

See

https://newtonexcelbach.wordpress.com/2014/02/16/cubic-splines-with-horizontal-data/

for the latest version that accepts input data in rows. It also has an optional Transpose arguments, so you can have input data in rows with output in columns, or vice versa.

LikeLike