Curve Fitting, Confidence Intervals and Envelopes, Correlations, and Monte Carlo Visualizations for Multilinear Problems in Chemistry: A General Spreadsheet Approach

Abstract

A spreadsheet approach is used to fit multilinear functions with three adjustable parameters: ƒ = a1X1(x) + a2X2(x) + a3X3(x). Results are illustrated for three familiar examples: IR analysis of gaseous DCl, the electronic/vibrational spectrum of gaseous I2, and van Deemter plots of chromatographic data. These cases are simple enough for students in upper-level physical or advanced analytical courses to write and modify their own spreadsheets. In addition to the original x, y, and σy values, 12 columns are required: three for Xn(xi) values, six for Xn(xi)Xk(xi) product sums for the curvature matrix [α], and three for yi Xn(xi) sums for (b) in the vector equation (b) = [α](a). The Excel spreadsheet MINVERSE function provides the [e] error matrix from [α]. The [e] elements are then used to determine best-fit values contained in (a). These spreadsheets also use a dimensionless or reduced parameter approach in calculating weights, uncertainties, and correlations. Students can later enter data sets and fit parameters into a larger spreadsheet that uses Monte Carlo techniques to produce two-dimensional scatter plots. These correspond to Δχ2 ellipsoidal cross-sections or projections and provide visual depictions of uncertainties and correlations. The Monte Carlo results can also be used to estimate confidence envelopes for fitting plots.