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.
Newton Excel Bach, not (just) an Excel Blog 
Pingback: Non linear regression – 1 | Newton Excel Bach, not (just) an Excel Blog
Pingback: Daily Download 14: Curve Fitting 1 | Newton Excel Bach, not (just) an Excel Blog
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
LikeLike
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.
LikeLike
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
LikeLike