I have updated the ALGLIB Spline and Matrix Function spreadsheet to include 2D interpolation of tabular data, including both linear and cubic spline interpolation. The spreadsheet including the new functions with full open source code may be downloaded from AL-Spline-Matrix03.zip for Excel 2003 and earlier and AL-Spline-Matrix07.zip for the Excel 2007 and 2010 version.
Typical input and output is shown in the screenshots below:
CSpline2DA - click for full size view
LinSpline2DA and interpolation data
CSpline2DA and LinSpline2DA Output
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Interpolation Update | Newton Excel Bach, not (just) an Excel Blog
Thank you very much for providing this very nice piece of free software. I’ve downloaded the file “AL-Spline-Matrix03.zip” and used it with my German edition of Excel2002 to prepare graphs demonstrating different signal reconstruction techniques. Moreover, I wanted to unravel the secret behind Excel’s “smooth curve” checkbox. So I’ve compared the “crspline1da” (Catmull-Rom spline) as well as “cspline1da” (natural cubic spline) UDF you developed from ALGLIB (I hope my understanding is correct) to the “cspline_interp” UDF contained in the free Excel-Addin called xNumbers. I’ve been using xNumbers for multiple precision arithmetics and all spectral calculations in Excel for years now. Additionally, I used the UDF provided in the xls-file “Smooth_curve_bezier_example_file.xls” that can be downloaded from http://www.xlrotor.com/resources/files.shtml. I used a pure sine with A=1 and f=3/8 to create 129 equidistant samples from 0 to 16, so that the spectrum of the first 128 values of that series only containes two lines at f=+/-0.375. I then used every 8th sample of that series to reconstruct a full time series by applying the different spline UDFs.
The main results are:
1. cspline1da perfectly agrees with cspline_interp.
2. Smooth_curve_bezier… perfectly agrees with the result of Excel’s “smooth curve” checkbox.
3. crspline1da agrees with Smooth_curve_bezier… only on all the inner spline segments but differs on both end segments, regardless of the boundary conditions chosen. Moreover, even if a periodic boundary condition is chosen in crspline1da, the spectrum shows a considerable background typically caused by a non-perodic signal.
4. The spectrum of the time series recontructed using the Smooth_curve_bezier… UDF is a line spectrum, as is even the one reconstructed from linear interpolation between the 17 samples. Of cause, this is due to the fact that 3/8 exactly matches an FFT frequency bin.
Concluding, I think something is going wrong in the first as well as last segment of a spline created by crspline1da. I do not know whether this happens in VBA only or in the ALGLIB C-code, too. I’ll check out the Python version ASAP.
Please let me know if you want my xls-file or screenshots.
Sincerely Yours
Georg
LikeLike
Georg – if you could send a copy of the xls file to dougaj4 at gmail, that would be helpful.
Thanks
Doug
LikeLike
Hi Doug,
thank you for offering help. As you suggested in your PM (thanks again for the PM, I forgot to tick the “notify”-checkbox…), I will contact AlgLib (this weekend). I’ll send you my files immediately after having finished this post.
From the “1st derivative” chart (in the file “drgst_interpol.xls” as well as in the screenshot named “drgst__aliasing-1deriv-512.png”) it seems clear what happens: the values of the 1st derivative of the AlgLib-CR spline at both end points do not coincide with the values of the slopes of the two straight lines defined by the first and last two data points, resp. The second algo I mentioned in my original post (in the file “drgst__smooth_curve_bezier_example_file.xls”) meets this requirement and thus exactly reproduces MS-Excels “smooth curve” functionality. There was a “funny” thing with this xls-file: after ahving added a second chart I tried to add the spline but always got an error message. Finally I found out that this second chart is internally referenced by “Chart 7”! That’s why I added the ListChart()-UDF to that file…
I forgot to mention where the “xNumbers”-addin can be downloaded from:
early versions: http://digilander.libero.it/foxes/SoftwareDownload.htm
current versions: http://www.thetropicalevents.com/Xnumbers60.htm
I saw a post on a spreadsheet meant for solving quartic polynomials on your blog. According to my experience gained from converting GPS-cartesian coordinates into geographic coordinates, Excels numerical accuracy may not be high enough to reliably solve that problem when using the formulae developed by Lodovico Ferrari 450 years ago. I will post there this weekend…
Regards
Georg
LikeLike
Pingback: Charts for discussion | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 13: The ALGLIB maths library and Excel | Newton Excel Bach, not (just) an Excel Blog