Curve Fitting, Confidence Intervals and Envelopes, Correlations, and Monte Carlo Visualizations for Multilinear Problems in Chemistry: A General Spreadsheet Approach
Paul J. Ogren, Brian L. Davis, Nick Guy · Journal of Chemical Education · 2001
A spreadsheet approach is used to fit multilinear functions with three adjustable parameters: ƒ = a 1 X 1 ( x ) + a 2 X 2 ( x ) + a 3 X 3 ( x ). Results are illustrated for three familiar examples: IR analysis of gaseous DCl, the electronic/vibrational spectrum of gaseous I 2, 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 X n ( x i ) values, six for X n ( x i ) X k ( x i ) product sums for the curvature matrix [α], and three for y i X n ( x i ) sums for ( b ) in the vector equation ( b ) = [α]( a ). The Excel spreadsheet MINVERSE function provides the [ε] error matrix from [α]. The [ε] elements are then used to determine best-fit parameter values contained in ( a ). These spreadsheets also use a "dimensionless" or "reduced parameter" approach in calculating parameter 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 parameter uncertainties and correlations. The Monte Carlo results can also be used to estimate confidence envelopes for fitting plots.