On the Web edited by
JCE WebWare: Web-Based Learning Aids
William F. Coleman
Excel Tutorial: Using the Least-Squares Method To Calculate Unknown Concentrations and Error Excel Tutorial: Using the Least-Squares Method To Calculate Unknown Concentrations and Error
Wellesley College Wellesley, MA 02481
Edward W. Fedosky University of Wisconsin–Madison Madison, WI 53715
Using the Least-Squares Method To Calculate Unknown Concentrations and Error
by J. J. Harynuk* and J. Lam Department of Chemistry, University of Alberta, Edmonton, AB T6G 2G2, Canada; *
[email protected] Keywords: Audience: Second-Year Undergraduate; Upper-Division Undergraduate. Domain: Analytical Chemistry; Laboratory Instruction. Pedagogy: Computer-Based Learning; Internet/WebBased Learning. Topics: Calibration; Laboratory Computing / Interfacing; Quantitative Analysis Requires Web browser and Excel software
It was observed that undergraduate chemistry students lacked the basic skills required to evaluate data, generate a calibration curve using least-squares, and calculate the error in the value that they were reporting in their third-year analytical instrumentation laboratory. Additionally, many students were unable to break a somewhat complex equation down into a series of relatively simple calculations to be performed with a spreadsheet. To address these problems, an online tutorial was developed. This tutorial guides students through the construction of a spreadsheet that performs least-squares analysis to find the best-fit line through a series of data points. These calculations can be performed automatically in many spreadsheets, but here we use a somewhat longer process. This has two benefits for students: they can see what the high-level functions are doing, and they learn how to translate an equation into a series of calculations that can be implemented with a spreadsheet. After working through the tutorial, the students have constructed a spreadsheet that will serve them throughout the remainder of the semester, and have also gained significant exposure to the use of spreadsheets. The tutorial comprises several parts. First, there is the Excel tutorial Using the Least-Squares Method To Calculate Unknown Concentrations and Error (Figure 1). This is a Macromedia flash file that can be embedded as an object in any Web-enabled course, and it is easily accessible and functional on common
An Excel Tutorial
Figure 1. Screenshot from the MS Excel tutorial Using the LeastSquares Method To Calculate Unknown Concentrations and Error.
Web browsers on both the PC and Mac platforms. The tutorial runs about 15 minutes. The second portion is a handout The Calibration Curve that provides some information about types of calibration and also gives a quick overview of the mathematics used in the tutorial. Sample problem sets with imperfect data (some points saturating detectors, simulation of swapped standard vials, standards prepared at concentrations below the detection limit), a copy of a calibration lab exercise, and an additional instructor handout are also provided to aid instructors in implementing this tutorial in their courses. Supporting JCE Online Material http://www.jce.divched.org/Journal/Issues/2009/Jul/abs879.html Abstract and keywords Full text (HTML and PDF) Supplement
Find Excel Tutorial: Using the Least-Squares Method To Calculate Unknown Concentrations and Error in the JCE Digital Library at http://www.jce.divched.org/JCEDLib/WebWare/collection/ reviewed/JCE2009p0879WW/index.html
© Division of Chemical Education • www.JCE.DivCHED.org • Vol. 86 No. 7 July 2009 • Journal of Chemical Education
879
