The Model Linear Least Squares Analysis
David L. Zellmer
Chemistry 102
February 11, 1999
The various tutorials on Linear Least Squares Analysis (LLS) found on the Chemistry 102 web site show how LLS was done on various versions of Excel and using different built-in functions available in Excel. From time to time I will publish the "current" recommended model for doing LLS based on the latest software and on student experiences with the previous tutorials. This model was done on Microsoft Excel 98 for the Macintosh. The equivalent version of Excel for the PC is found in Microsoft Office 97. When Office 2000 comes out later this year we may need to upgrade this example.
Recommendations for using this model:
When you use this model, please replace labels as needed to reflect the experiment you are running.
Use the Chart Wizard to graph your data. Be sure the chart title and axes listed on the graph are correct for your data analysis.
After graphing the data, select the data points on the chart, then use Chart/Add Trendline... to draw the line through the data. I have changed the default appearance of the data points and of the Trendline by making different choices using the various formatting tools available.
The Excel Draw tools were used to add the lines showing how the unknown concentration can be read from just looking at the chart. (You do not need to add these lines.) Reading the value of the unknown concentration from the chart serves as a good check on the accuracy of your formulas.
Once you have typed in this model exactly as shown above, you will be able to change the number of rows in the data block by using Insert/Rows for more data points or Edit/Delete... rows to reduce the number of data points. If you change the rows in this way, the formulas in the LLS Analysis block will change to reflect the row changes.
If you simply delete the model data and type in your own, be sure to go through the formulas and change the references to point to your new data.
The Documentation Block does not need to be copied into your spreadsheet. The cell references given are simply text labels, and will not change if you change the data rows from those used in the Model.
The equations and variable names used in this model are from Skoog, West and Holler, Fundamentals of Analytical Chemistry, 7th Ed. For a summary of these equations see LLS Formulas.