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: Simple linear least squares fit


AL_LinestCW: Weighted and constrained linear least squares fit.


AL_FitPoly Simple polynomial least squares fit

and AL_PolyFitCW:

AL_FitPolyCW Weighted and constrained polynomial least squares fit.

These functions have been added to the AL-Spline-Matrix spreadsheet, which may be downloaded from: or (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.

This entry was posted in AlgLib, Excel, Maths, Newton, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

2 Responses to ALGLIB linear and polynomial fitting functions

  1. Pingback: Non linear regression – 1 | Newton Excel Bach, not (just) an Excel Blog

  2. Pingback: Daily Download 14: Curve Fitting 1 | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s