Short Course Listings
Short Course

Course Information
Course Title: Examples of Analytical Data Treatment Using Microsoft® Excel™: Part 2 – More Advanced Topics
Categories: 1 - Chemometrics
2 - Data Analysis
3 - Statistics
4 - Teaching Analytical Chemistry
Instructor(s): Mark Stauffer Course Number: 110
Affiliation: University of Pittsburgh - Greensburg
Course Date: 03/08/2016 - Tuesday Course Length: 1/2 Day Course
Start Time: 01:00 PM End Time: 05:00 PM
Fee: $300 ($400 after 2/12/16) Textbook Fee:

Course Description
This course goes beyond Part 1 by using Microsoft® Excel™ for performing weighted, multiple linear, and multivariate regressions for calibrations and related tasks, matrix functions, ANOVA, and other, more advanced operations. As in Part 1, topics are chosen at the instructor’s discretion, and suggestions from participants are welcome. A functional knowledge of Excel™ is assumed. Participants will receive relevant course materials. Purchase of “Excel for Chemists: A Comprehensive Guide”, Third Edition, by E. Joseph Billo (Wiley-VCH, New York, NY, 2011) (ISBN 978-0-470-38123-6) is OPTIONAL.

Target Audience
Professionals in industry, academia, government, and health-related areas, undergraduate chemistry and related science majors, graduate students in the sciences – in other words, anyone who wants experience with more advanced experimental data/results manipulation with Microsoft® Excel™.

Course Outline
There are more advanced operations useful to the analytical chemist which Microsoft Excel™ can perform. As with any course on any subject or topic, there are many topics available, but only so much time to cover these in the context of a half-day course. Thus, I present the following list of potential topics that can be covered and from which the instructor and participants can select (NOTE that the bulleted items are NOT listed in any particular sequence, and that not all of them can and will be covered):

• Multiple linear regression (e.g., for use in calibration): using Excel’s™ LINEST function and the Regression tool in Excel™’s Analysis ToolPak
• Using Excel™ to perform a weighted regression
• Using Excel’s™ Analysis ToolPak for performing one-way and two-way (without and with replication) ANOVA
• Performing Fourier transforms via the Analysis ToolPak
• Introduction to Excel™’s matrix functions for vector and matrix manipulations
• Multivariate regressions, particularly related to inverse (P-matrix; i.e., C = AP) and indirect (K-matrix; i.e., A = CK) methods (“hard modeling”), and principal component (PCR) and partial least squares 2 (PLS2) methods (“soft modeling”)
• Numerical differentiation (e.g., for first-derivative plots of pH titration curves) and integration
• Using Excel™’s GoalSeek and Solver tools (e.g., for spectrophotometric analysis of mixtures in which the spectra of the components overlap severely, for optimization, for nonlinear least-squares curve fitting, and for solving 2nd-order and higher-order polynomials)
• Exploring Pivot Tables and Pivot Charts

The aforementioned list is only a fraction of possible topics coverable in this course. There are, of course, topics not listed here that participants may want to learn. Thus, I encourage participants to contact me directly (, prior to the short course, about any topics they would like to see covered in this short course, whether or not they are indicated in the aforementioned list.

I intend for this short course to be interactive, in that the instructor and the participants may share and discuss, in an informal and relaxed manner, the many uses of Excel™ in analytical chemistry. The instructor may know things that the participants may not, and the participants may have ideas for using Excel™ and its many functions and tools that the instructor does not know. Thus, participants will be asked to share their expertise with the group, rather than the instructor talking at the participants for the entire short course. Additionally, and most important, I intend for this short course to be a hands-on course in which the participants get to work with various exercises that incorporate the topics that are covered. The instructor will develop exercises for the participants to hone their skills with Excel™ and its myriad capabilities. These exercises will be part of the package that the participants receive as part of their course registration.

Course Instructor's Biography
Dr. Mark T. Stauffer is Associate Professor of Chemistry, and the immediate former 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 over 80 students and produced nearly 50 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), a printed paper (2003, 80, 65-67) and an online paper (April 2008 issue) in the Journal of Chemical Education, and a chapter titled “Limiting Reactants in Chemical Analysis: Influences of Metals and Ligands on Calibration Curves and Formation Constants for Selected Iron-Ligand Chelates”, in Stoichiometry and Research – the Importance of Quantity in Biomedicine (A. Innocenti, Ed.; InTech Publishing, 2012; ISBN 978-953-51-0198-7). He was Guest Editor for the Special Issue, “Selective Chelating Agents”, of the peer-reviewed online journal Sensors. He has presented this short course in various formats since Pittcon 2009, and has presented many workshops on graphing and data/results manipulation using Excel™ to undergraduate science majors and faculty colleagues at Pitt-Greensburg since 2001. Dr. Stauffer has presented (with colleague Dr. Christine McCreary) a short course on Excel™ in May 2002, sponsored by the Spectroscopy Society of Pittsburgh. His most recent short course was presented to the Global Analytical Group of the Avery Dennison Corporation on April 9, 2014. 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, dog, and human hair as well as by humic, tannic, and fulvic acids, antioxidant activity and polyphenol reactions with metal ions, studies of protonation constants for ligands and stability constants for metal-ion chelates, UV-visible and atomic absorption spectrophotometry, electrochemistry, analytical method development, calibration, and validation, field analytical methodology, and chemometrics. He is a member of the American Chemical Society, the Society for Analytical Chemists of Pittsburgh (SACP), and the Spectroscopy Society of Pittsburgh (SSP). He is presently Executive President-elect of the Gamma Sigma Epsilon national chemistry honor society, and will become Executive President in Fall 2015. He is a former President of the Pitt-Greensburg Faculty Senate and served as Chair of the Division of Natural Sciences, Mathematics, and Engineering at Pitt-Greensburg from July 2008 through June 2014, and is again a regular full-time faculty member – with more time to devote to this short course.