Course Information
Course Title: Examples of Analytical Data Treatment Using Microsoft® Excel™
Categories: 1 - Chemometrics
2 - Data Analysis
3 - Teaching Analytical Chemistry
Instructor(s): Mark Stauffer Course Number: 68
Affiliation: University of Pittsburgh at Greensburg
Course Date: 03/12/2012 - Monday Course Length: 1/2 Day Course
Start Time: 08:30 AM End Time: 12:30 PM
Fee: $235 ($335 after 2/13/12) Textbook Fee: $70

Course Description
This course will deal with applications of Microsoft® Excel™ to various areas of analytical chemistry, such as statistical treatment of data and results, method calibration, detection and quantitation limits, analysis of mixtures, and titration curves. Topics will be chosen at the instructor’s discretion. A basic operational knowledge of Excel™ is assumed. Participants will be exposed to spreadsheet setup, plotting data, and using Excel’s math functions to generate results. Participants will receive materials related to the topics covered in this short course, and a copy of “Excel for Chemists: A Comprehensive Guide”, Second Edition, by E. Joseph Billo (Wiley-VCH, New York, NY, 2001) (ISBN 0-471-39462-9).

Target Audience
Professionals in industry, academia, government, and health-related areas who want a refresher on using spreadsheets for their projects, undergraduate chemistry and related science majors, graduate students in the sciences – in other words, anyone who wants some experience in manipulation of experimental data and results with Microsoft® Excel™.

Course Outline
1.  Topic 1:  (Tentative) Calibration and spreadsheets…

a. Review of calibration and calibration curves, linear regression of calibration data, limits of detection and quantitation, R-square, and standard error of the estimate.
b. LINEST – Excel’s approach to linear regression of calibration data.
c. Hands-on project 1:  Using calibration data in exercises provided by the instructor, perform a linear regression of the signal/concentration data pairs, construct the corresponding calibration plot, and determine limits of detection and quantitation from the regression results.
d. Group discussion of the results obtained in hands-on project 1.

2.  Topic 2:  (Tentative) Spectrophotometric analysis of mixtures using Cramer’s Rule and least squares minimization – the combination of spreadsheets, matrix algebra, and Solver…

a. Review of spectrophotometric analysis of mixtures, matrices, and Cramer’s rule, leading into…
b. MDETERM – the ExcelTM way to solve matrices.
c. More matrix functions:  MINVERSE and MMULT.
d. Least-squares minimization and the Solver optimization tool in Excel™:  another approach to spectrophotometric analysis of mixtures. 
e. Hands-on project 2:  Using the data in exercises supplied by the instructor, determine the concentrations of the individual components of an absorbing solution containing at least two distinct chemical species.
f. Group discussion of the results obtained from hands-on project 2.

3.  More group discussions from Topics 1 and 2, more applications of ExcelTM to chemical analysis, as time permits, more questions and ideas…

Course Instructor's Biography
Dr. Mark T. Stauffer is Associate Professor of Chemistry and Chair of the Division of Natural Sciences, Mathematics, and Engineering at the University of Pittsburgh at Greensburg. He received his B.S. (1979) and Ph.D. (1998) degrees in Chemistry from the University of Pittsburgh, and was employed by the Ethyl Corporation during the 1980s as an analytical chemist in Ethyl’s Research and Development Department. Dr. Stauffer has held teaching positions at the University of Wisconsin-Madison, Shippensburg University, and Carnegie Mellon University. He joined the Pitt-Greensburg faculty in 2001 as an Assistant Professor of Chemistry, and was promoted to Associate Professor with tenure in 2007. Since 2002, Dr. Stauffer has managed a successful undergraduate research effort at Pitt-Greensburg that has, to date, involved nearly 60 students and produced over 30 oral papers and posters delivered at various technical conferences, three publications in Spectroscopy Letters (2007, 40(3), 429-437; 2007, 40(3), 439-452; and 2010, 43(7), 597-601), and a printed paper (2003, 80, 65-67) and an online paper (April 2008 issue) in the Journal of Chemical Education. His research interests involve profiling of metals and anions in abandoned mine drainage, other natural waters, and soils, determination of metals in foods, beverages, and animal and human hair, phytoremediation of metals in waters and soils, chelation of metal ions by components of melanin in cat and human hair as well as by humic, tannic, and fulvic acids, antioxidant activity and polyphenol reactions with metal ions, UV-visible and atomic absorption spectrophotometry, electrochemistry, analytical method development and validation, and field analytical methodology. He is a member of the American Chemical Society, the Society for Analytical Chemists of Pittsburgh (SACP), the Spectroscopy Society of Pittsburgh (SSP), and the OnSite Scientific Board. He is a former President of the Pitt-Greensburg Faculty Senate and has been Chair of the Division of Natural Sciences, Mathematics, and Engineering at Pitt-Greensburg since July 2008.