A cubic spline provides a good approximation to a smooth curve, and alternative versions are available for free download (see Daily Download 22: Splines and Curves, Update to AL-Spline-Matrix, and xlwSciPy 1.09 – update for xlwings 0.10 and Scipy 0.18.1), but if a curve has a sharp change of direction a single cubic spline will deviate significantly from the required values near the change. An example of curves where this is a problem is a reinforced concrete moment-curvature diagram, which has sharp changes of slope at the cracking moment, and also at the reinforcement yield point.
To deal with curves of this sort, I have added an MSplineA user defined function (UDF) to the CSpline2 spreadsheet, which may be downloaded (including open-source code) from:
This function allows a curve to be divided into any number of segments, each of which may be either linear or cubic splines. Input and output details, and a typical example are shown in the screen shots below:
Required input ranges are the X and Y values of the input data, a list of spline segments, listing the last node number of each segment, the spline type to be applied (1 = linear or 3 = cubic), and end curve or slope details for cubic splines, and the X values where interpolation is required:
Output for this example is shown below:
Looking more closely at the region near the cracking moments it can be seen that the MSplineA function has given a good approximation to the input values, whereas a single cubic spline deviates significantly:
Doug. I also had problems with splines like you mention and tried Akima’s method. It often looks more natural. Worth adding to your repertoir.
Bob – that’s actually an option with existing spreadsheets here, either using Alglib (https://newtonexcelbach.com/2010/06/07/alglib-spline-functions/) or Python Scipy and xlwings (https://newtonexcelbach.com/2016/03/20/xlscipy-with-xlwings/).
I’ll have a look and see how they compare with my function.