Tutorial on using Microsoft Excel to perform a linear regression on simple data. This tutorial will provide step-by-step instructions for using Excel to perform a least squares fit of a straight line to a data set. You will learn how to obtain the coefficients m, the slope, and b, the y-intercept, of the best linear fit of the form Y = mX + b. You will also see how to obtain the standard error in the coefficients.
I have written a short set of Notes on Linear Regression that outline a simple way to derive the equations for a linear regression. The notes also give a code fragment for programming a linear regression in Python using the SciPy modules.
The equations used (behind the scenes) by Excel in calculating the slope and intercept of the "best" straight line fitting a set of approximately linear data are usually derived in a course on probability and statistics. For Calculus level discussion with an emphasis on data analysis see Introduction to Error Analysis: The Study of Uncertainties in Physical Measurements (Second Edition, 1997) by John R. Taylor, University of Colorado, Boulder. Publisher: University Science Books. Taylor also discusses in detail the question of whether or not the fit is "good" which is closely related to the question of whether there is a correlation between the two variables. In physics this question is often answered for us on theoretical grounds. For example, velocity and time for a freely falling body are always going to be closely linear and well correlated unless it falls fast enough for air resistance to be important.
To begin the tutorial click Start