I have updated the Alglib spline-matrix spreadsheet to use the latest Alglib release, using the Python version, in conjunction with the Excel-Python add-in. To use the spreadsheet requires:

Python ver 2.7 (or 2.6)
Excel-Python
Alglib with Python interface

In addition to using the latest Alglib library, the new spreadsheet updates the functions, providing added functionality in applying weights and constraints to fitted functions, and additional examples. The new spreadsheet may be downloaded from xl_Spline-Matrix.zip, including full open-source VBA and Python code linking to the Alglib library. The Alglib code may be downloaded from the link above.

The functions included in the spreadsheet are listed in the screenshot below:

This post will look at the fitting functions.  The spline interpolation functions and matrix functions will be described in a later post.

The xl_LinFit function is equivalent to the Excel LinFit function, but also allows weights and constraints to be applied to the fitted data:

The example below shows a quadratic curve fitted to scattered data with various options and constraints.  Results from the Excel LinEst function are also included, which are the same as the Alglib function without weights and constraints.

The xl_LinFit function can be used to fit polynomial curves, as seen above, but this is more conveniently done with the xl_PolyFit function, which also gives much better results for high order polynomials.

The screenshots below show a 15th order polynomial fitted to a data generated from a cyclic function:

Two functions are provided for non-linear fitting. xl_NLFitFunc fits any named Python function to the input data. The function code must be in the file NLFuncs.py.

xl_NLFitText works in the same way except the function is entered as text on the spreadsheet, rather than as a Python function.

The example below shows output from both xl_NLFitFunc and xl_NLFitText applied to concrete shrinkage data. Note that both functions allow the use of weights and constraints.

The two screenshots below show the results of two alternative functions applied to the creep data. It can be seen that the second function gives a far better fit to the data.

The xl_NLFitFunc function also allows the use of gradient and hessian functions. See the alglib documentation for more details.

Finally the last two screenshots illustrate the use of the xl_NLFitFunc and xl_NLFitText functions to fit a fourth degree polynomial to scattered data: