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.

These functions have been added to the AL-Spline-Matrix spreadsheet, which may be downloaded from:

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.

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

5 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

  3. S Rijdt says:

    Hi dougaj4,

    Thank you very much for the informative post. Although it is a bit matured in the meantime very helpfull in my analysis.

    I have 2 questions:
    – In case I apply a AL_FitPOLY with output 2 this results in 5 items. What is the first or the fifth?
    – I’m relatively new to VBA coding, but is there a translation to VBA for AL-FitPOLY? In other words how can I apply te AL-Fitpoly in my code without using it as an UDF?
    – Is there a manual available how to use all these functions?

    Thanks in advance,
    Stan

    Like

    • dougaj4 says:

      Hi Stan,
      There is a detailed manual to the Alglib code at their site, which gives the report values as:
      TaskRCond reciprocal of task’s condition number
      RMSError RMS error
      AvgError average error
      AvgRelError average relative error (for non-zero Y[I])
      MaxError maximum error

      All the UDFs are written in VBA. To look at the code:
      Press Alt-F11 to open the VBA editor, and find the m_LinFit module, which includes the AL_FitPoly function. You can write your own VBA code to call the AL_FitPoly function, or to call the Alglib code directly.
      My code is quite simple; it converts the function input into the format required by Alglib, then calls the Alglib function, and converts the results to a format that can be returned to the spreadsheet.
      The Alglib code does all the hard work. It is quite complex, but does have good documentation. The VBA code is not being updated now, but is still works.
      For using my functions the only documentation is what you can find here, and on the spreadsheet itself.

      I hope that asks your questions, but if not please ask.

      Like

      • Stan says:

        Hi Dougaj4,

        Your code is amazing, with some scraping I’ve managed to get the code running in VBA (the problem was an incorrect link between projects).

        I also managed to find the correct translation for the outputs.

        Thanks a lot!
        Stan

        Like

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.