Spreadsheet Approach to the Linear Least Squares Fit

Abstract

A method of programming two commercially available spreadsheet programs to calculate the linear-least-squares (LLS) line from a set of sample data is presented. Student data from a Quantitative Analysis chemistry course are used as examples. Files for both Excel for Macintosh and Quattro Pro for IBM compatibles are available online with this paper. These include blank spreadsheet templates, an example using seven xy pairs of student data with graphs of the data and best-fit line, and more advanced spreadsheet template files that incorporate the calculated standard deviation in the regression line’s slope and y-intercept, as well as the error about the line.