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
Using LinEst with polynomial curves
ALGLIB Polynomial fitting functions
Non-linear regression with the Excel Solver
ALGLIB non-liner curve fitting output
Newton Excel Bach, not (just) an Excel Blog 