Using Excel 'Solver' for Least Squares Regression - This page is in the process of development

 
Video Tutorials

These short step-by-step video tutorials show the detailed process of using the Excel Add-In 'Solver', together with real applications from different branches of science.

Core techniques using Excel 'Solver':

Applications in science:

  • Ideal gas law - 


QVA Tutorials (questions + video answers)

These short quizzes (5 - 10 questions each, with video feedback) provide a quick way of checking and increasing your knowledge and real understanding of the linearising of exponential data.


Background Theory

The target in obtaining a best-fit line to a set of data points is to reduce the sum of squares of the residuals (distance between a data point and the line) to a minimum value - the process of 'least squares'.

The way that Excel 'Solver' works (e.g. in fitting a straight line) is to change the parameters that describe the line (slope and intercept) in a 'step by step' process until it reaches values that give a minimum value for the sum of squares. This is called a process of iteration.

In Solver, you can identify an 'objective cell' which holds the sum of squares, and 'variable cells' whose values can be changed 'step-by-step' until the value in the 'objective cell' becomes a minimum.

The big advantage of using the iterative method in Solver is that it can be used to find best-fit lines for almost any form of theoretical equation - not just straight lines.

The process of iteration is like like trying to find the top of a hill in a thick fog. You could take each step upwards, step-by-step, until you reach a point where the next step, in any direction, will be downhill - you would then have reached a hill top. However, there might be a problem if there were several hills and you had ended up on one of the smaller ones! Due to the thick fog you could not know whether you were on the highest peak or not.

A similar situation can occur with the process of iteration, in that it is possible for Solver to stick at a 'local' minimum value that is not the lowest of all possible values! However, by choosing reasonable starting values, it is usually possible to use Solver effectively for most situations.

Solver can also be used for more complex calculations by adding extra constraints into the iterations. However, this complexity is not required for the process of regression.