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
Download file: http://interactiveds.com.au/software/LinEst%20Check.zip

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
Download file: http://www.interactiveds.com.au/software/Linest-poly.xls

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
Download files: http://interactiveds.com.au/software/NonLinFit-Solver.xls
http://interactiveds.com.au/software/AL-Spline-Matrix07.zip
http://interactiveds.com.au/software/AL-Spline-Matrix03.zip

Chart trend line and LinEst results

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