Since it was first published in 2009 my CSpline function has been the most popular download from this site. CSpline (and various associated functions) is used to fit a series of cubic curves through specified points, allowing interpolation along a smooth curve.
The latest version of the spreadsheet is:
http://www.interactiveds.com.au/software/CSpline2.zip
The original function is described at Cubic Splines. This was further developed in response to a reader query at Using Cubic Splines in Practice and Cubic Hermitic and Cardinal Splines, and most recently Spline Interpolation Alternatives provided some “on-sheet” methods with similar functionality.
Related spreadsheets were described in Daily Download 13: The ALGLIB maths library and Excel (covering the ALGLIB spline functions) and Daily Download 4: Continuous Beam Analysis (covering the use of cubic splines to model continuous beams, and the SplineBeam function).
The remaining curve generating spreadsheet is:
http://interactiveds.com.au/software/Catenary.zip
This includes a function to generate the coordinates of any specified catenary curve, with an option to find the tension force at any point. For more details see A catenary function.
Fitting a cubic spline to five data points
Cubic Spline and alternative Hermite Splines
Interpolated and fitted curves with spikey data
Catenary UDF
Newton Excel Bach, not (just) an Excel Blog 
Doug,
Excellent blog here, much appreciated… I am working on a calculation tool that needs to apply a spline function (I will probably apply the hermite version), and I figured I’d apply yours, but one part has me somewhat stumped. I’ll admit I’m not a very strong programmer, so this might be a real newbie question, but I’m not even sure how I’d find a solution via search engine hunting…
Your cspline2 modules require a vertical array to work, and I need to set up my spreadsheet horizontally for a few reasons, one of them being aesthetic, and the other having to do with expectations (how people are expecting to enter data).
I have tried application.transpose to swap the inputs into a vertical array for use in your modules, without success, and I have tried inverting the data in the module (swap the row for columns) without success.
Clearly, I could set up an intermediate array where the data is all transposed and then run through the module and then it’s passed back vertically, and then transposed again once it’s in the spreadsheet, but that seems like an inelegant approach.
Can you suggest a method to pull it in from a horizontal oriented array, convert in VBA, run through your module, and then convert in VBA again so that it is passed to the spreadsheet in both ways horizontally? I actually only need the interpolation portion of the module, so I was planning to strip the rest out once I have it running.
Thanks,
—Michael
LikeLike
Michael – Thanks for the feedback.
There are a few non-obvious issues with using the Transpose function from VBA, but it’s a useful addition to allow horizontal input ranges so I have added a routine to transpose the ranges if the number of columns is greater than the number of rows.
At the moment it still outputs a vertical array, but I can easily add an option to allow horizontal output instead. I have added the transpose function to CSplineA and CSplineH only so far.
Could you download from:
http://interactiveds.com.au/software/CSpline2-Transpose.xlsb
and let me know if that works with your data.
LikeLike
Doug,
Thanks, that is working. I can use the transpose function on the sheet to get it horizontal.
If you were to add a vertical/horizontal option, that would probably be ideal. I suspect that most people would want it to match the orientation of the original arrays.
I read through your code for how you did the transpose, and I see where I failed in my attempt. I still am vague on types of data, and need to get that a bit better understood to work with arrays better.
—Michael
LikeLike
Michael – I have uploaded an update that now transposes the output to the same orientation as the Xint array, with an optional argument so you can get a column output with row input if you want to (although I can’t think why you would!).
It isn’t thoroughly tested, but it worked on the functions I tried it on.
Same download address as before.
LikeLike
Pingback: Cubic splines with horizontal data | Newton Excel Bach, not (just) an Excel Blog
Pingback: Cubic splines with descending x | Newton Excel Bach, not (just) an Excel Blog
Pingback: Cubic splines with jagged data | Newton Excel Bach, not (just) an Excel Blog