## ALGLIB linear and polynomial fitting functions

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:

AL_Linest:

AL_Linest: Simple linear least squares fit

AL_LinestCW:

AL_LinestCW: Weighted and constrained linear least squares fit.

AL_PolyFit:

AL_FitPoly Simple polynomial least squares fit

and AL_PolyFitCW:

AL_FitPolyCW Weighted and constrained polynomial least squares fit.

AL-Spline-Matrix03.zip or AL-Spline-Matrix07.zip (recommended version for anyone running XL 2007 or later).

Output from the new functions is shown in the screen-shots below:

AL_Linest and AL_LinestCW Output

AL_PolyFit and AL_PolyFitCW Output

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.