As promised here: Fitting high order polynomials this post presents details of four Excel User Defined Functions (UDFs) linking with ALGLIB functions for least squares fitting of linear and polynomial functions. The new functions are:
These functions have been added to the AL-Spline-Matrix spreadsheet, which may be downloaded from:
Output from the new functions is shown in the screen-shots below:
The AL_Linest function offers no obvious advantage over the built in Excel Linest function, but the other three functions have significant extra functionality:
- AL_LinestCW allows weighted and constrained linear least squares fitting.
- AL_Polyfit and AL_PolyfitCW are specifically written to fit a polynomial function of any order, and appear to be much more stable with high order polynomial functions than any of the built-in methods in Excel.
- In addition AL_PolyfitCW allows constrained and weighted fitting.
The current VBA version of ALGLIB returns the polynomial data in barycentric form, rather than polynomial coefficients, and the Excel UDF converts this to interpolated function values using the ALGLIB BarycentricCalc function. The latest version of the library (3.2) will also return the polynomial coefficients, but this version has yet to be released in VBA. All versions of the ALGLIB library may be freely downloaded from the ALGLIB site.