Today’s download topic covers fitting a straight line or curve to scattered data, using the “least squares” method.  Fitting a spline curve to pass exactly through a series of points will be covered in a few days.

Use of the LinEst function, and differences in the results compared with the chart based fitting function was described in : Chart Trend Lines and the Linest Function

The LinEst function can also be used to fit certain curves (such as polynomials, exponential, power and logarithmic functions) to a set of data, as described at: Using LINEST for non-linear curve fitting

This technique was extended for data with gaps in: Using LinEst() on data with gaps

Fitting high order polynomials can give unexpected results, as described at: Fitting high order polynomials.  The ALGLIB library was found to give much more stable results than using the LinEst function for polynomials of 5th order and higher.  VBA interface functions allowing the ALGLIB functions to be used from Excel are described at: ALGLIB linear and polynomial fitting functions.  These functions are included in the AL-Spline-Matrix download files below.

Non-linear regression using the Excel Solver was covered at: Non linear regression – 1 and using the ALGLIB Library at Non linear regression – 2; ALGLIB functions
http://interactiveds.com.au/software/AL-Spline-Matrix07.zip
http://interactiveds.com.au/software/AL-Spline-Matrix03.zip Using LinEst with polynomial curves Regression for data with gaps Fitting 7th order polynomial ALGLIB Polynomial fitting functions Non-linear regression with the Excel Solver ALGLIB non-liner curve fitting output

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

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