 |
The Linear Least Squares Analysis Master Page
©David L. Zellmer, Ph.D.
Department of Chemistry
California State University, Fresno
|
Note added March 3, 1999: The following tutorials follow an historical pattern beginning with the earliest days of Excel when fewer advanced functions were available. The earliest tutorials follow a "bare bones" approach which has the student create his or her own regression equation and gives complete control over what is plotted. Even with the advanced features of the current versions, it is not possible to generate and plot the error limits without using the techniques shown here.
The LINEST() function used in these earlier tutorials contains many statistical features and is now recommended only for advanced work, such as multiple linear regression. If you think you may need to use LINEST(), you may want to work through these earlier examples.
For those students who want the latest and cleanest version of Least Squares Analysis that generates a working curve and computes the concentration (x-axis) error from an instrument response, go directly to The Model LLS Spreadsheet, which uses Office 98 for the Macintosh (almost identical to Office 97 for the PC). The instructions there are very brief, and assume some familiarity with basic formulas and graphing in Excel.
Choose the Frames or Non-Frames versions of the tutorials below. Frames allows you to see the spreadsheet example while scrolling the directions in a separate window. Non-frames is better for printing out the tutorial, or for use by those whose browser may not support frames.
- LLSBasic01: A brief tutorial on basic graphing and using the LINEST() function in Microsoft Excel .
Frames version
Non-Frames version
- LLSBasic02: Building on the basic techniques of graphing and use of the LINEST() function we can add some features to make the actual line appear. From the numbers generated we can even estimate the errors in the fit.
Frames version
Non-Frames version
- LLSBasic03: Building on the basic techniques of graphing and use of the LINEST() function, then plotting multiple series and estimating errors, we now will use more Excel statistical functions such as STEYX() to calculate the errors of our Linear Least Squares fit.
Frames version
Non-Frames version
- LLSBasic04: Starting with the basic techniques of graphing and use of the LINEST() function then plotting multiple series and estimating errors, we now will use several Excel statistical functions to calculate the errors of our Linear Least Squares fit using the complete error formula for the 95% Confidence Limits about regression.
Frames version
Non-Frames version
- Example 4_9 from SWH 7th Edition. The statistical formulas from the textbook are modified with Excel functions for ease of calculation. The error of a concentration read from a working curve is computed.
Non-frames version only
- LLS Formulas. The statistical formulas from a statistics text and from Skoog, West, and Holler, Fundamentals of Analytical Chemistry, 7th Ed. are collected and compared with their Microsoft Excel equivalents.
Non-frames version only
- LINEST with extended statistics and polynomial fits. The LINEST function is capable of far more than just slope and intercept. The examples show a second order fit to curved data and include calculation of the errors when using this fit as an analytical working curve.
Non-frames version only
- The Model LLS Spreadsheet (March 3, 1999). This spreadsheet is similar to Example 4_9 above, but uses generic data and does not use t-statistics to generate confidence limits. Familiarity with Excel 98 for the Macintosh, or Excel 97 for the PC is recommended for this example. Students are encouraged to use this spreadsheet as a basic model for plotting and fitting working curves. Additional features can be added as needed.
Non-frames version only
- How to Make a Second Order Fit (April 18, 1999). What do you do when your data curve over a bit rather than making a straight line? This tutorial shows how to use Excel 98 for the Macintosh (or Excel 97 for the PC) to fit a quadratic equation to the data. A method of estimating the error involved is also presented. The method shown is more practical than the more rigorous LINEST with extended statistics and polynomial fits listed above.
Non-frames version only
For questions or comments, contact Dr. David Zellmer at david_zellmer@csufresno.edu.
Last Updated: 18 April 1999