In my previous post on splines I described a quick and dirty approach to adjusting a cubic spline to fit monotonic data, which did not meet with universal approval, so I have followed up Mike Woodhouse’s link: (http://en.wikipedia.org/wiki/Monotone_cubic_interpolation) which gives a more mathematically correct procedure.
I have added three new functions to the spreadsheet CSpline2.xls, including full open source code.
The functions are: CHSplineA
CHSplineA Function (click for full size view)
CHSplineA fits a Cubic Hermitic Spline curve through a series of XY points, with the option to force monotonic output. Typical input and output is shown below:
CHSplineA input and output
Output graphs from CHSplineA
The function CardSplineA fits a Cardinal Spline to a series of XY points. The code is based on a perl script posted on the Wikipedia article:
CardSplineA Function
The Cardinal Spline allows functions with non-monotonic X values. There are three options for the interpolation points; either An L value (specifying a segment number and position of the point along the segment length), or X or Y values.
CardSplineA output
CardSpline Graph
Finally I have adapted Lori Miller’s code to extract XY data from chart series, the main change being to convert it into an array function, which should improve performance:
ChartSplineA Function
ChartSplineA Output
Newton Excel Bach, not (just) an Excel Blog 
Apologies if I inadvertently caused you any pain, but this is great!
The results, especially the first example, just show how much it may matter what kind of fit you apply to your data. To be honest, I prefer the non-mono “curvier” curves aesthetically, but for something like our cumulative probability function at least the monotonic version is guaranteed to be, er, monotonic.
Again, awesome job. 🙂
LikeLike
Mike – thanks for the comments and the incentive to go back and do it properly 🙂
LikeLike
Pingback: Revision to CSpline2.xls | Newton Excel Bach, not (just) an Excel Blog
Thanks for making this available Doug — it’s really useful stuff.
I was messing around with the CHSplineA function (with Mono = true) and I was surprised by the results if the inputs are monotonic but not strictly so. For example, if I set x = {1, 2, 3, 4} and y = {0, 0, 1, 1} then the spline evaluated at 1.5 is -0.0625. I guess I was expecting it to be zero there. If I make the inputs strictly increasing with, for example, x = {1, 2, 3, 4} and y = {0, 0.001, 0.9999, 1} then the resulting spline is monotonic as expected.
Am I missing something here?
LikeLike
It looks like there must be some bug somewhere. If you increase your second y value from 0 to 1E-150 then the output results are all monotonic! I will have a closer look when I have time, but in the meantime if you ensure that all your segments have a positive slope (even a very tiny one) it seems to work OK.
LikeLike
Would it be possible to make a version that just outputs the interpolated y value for a given x value. The code looks very thorough and I would like to use it, but don’t want to have to ruin my spreadsheets with six columns of output that I’m not interested in….
LikeLike
Iain, it already does that! If you look at the results in columns G and H, that is the Y and L values for the X values in column F. If you don’t want the L results just select one column, instead of two, when you enter the array function. In the spreadsheet the X values in column F are read from column D, but they don’t need to be, you can enter them by hand, or generate them any other way. The only thing to watch out for is that they must be in a sequence matching the line shape, so in this case they need to be all increasing up to the maximum value, then all decreasing.
LikeLike
Pingback: Daily Download 22: Splines and Curves | Newton Excel Bach, not (just) an Excel Blog