Information • Textbooks • Media • Resources edited by
Computer Bulletin Board
Steven D. Gammon
Using Excel Solver: An Addendum to the HCl Infrared Spectrum Experiment
University of Idaho Moscow, ID 83844
Mark Iannone Department of Chemistry, Millersville University, Millersville, PA 17551
The undergraduate physical chemistry experiment of analyzing the infrared spectrum of gas-phase HCl or DCl is unequaled at illustrating how the vibrational–rotational spectrum reveals molecular structure (1). We have recently used the following addendum to this experiment to introduce the use of Solver for data analysis. Solver is convenient, flexible, and widely available (2, 3), and physical chemistry students should find it very useful. The following equations establish the notation. The vibrational energy levels in cm᎑1, with a single anharmonic term, are E v /hc = ωv = ωe (v + 1/2) – ω e x e (v + 1/2)2 + …
(1)
᎑1
The rotational energy levels in cm , with one term to account for centrifugal distortion, are EJ /hc = ωJ = [B J ( J + 1) – D [ J ( J + 1)]2 + …]
(2)
In these equations, h is Planck’s constant and c is the speed of light in cm s᎑1. The position of a vibrational–rotational peak in the spectrum for the v = 1 ← v = 0 transition is given by ωpeak = (ωv=1 + ωJ′) – (ωv=0 + ωJ′′) = ωe – 2ωexe + B′J′(J′+ 1) – 2
2
B′′J′′( J′′+ 1) – D{[ J′( J′+ 1)] – [ J′′( J′′+ 1)] }
(3)
J′′ and J′ are the rotational levels in ground and upper vibrational states, respectively. The centrifugal distortion is assumed to be the same in both vibrational states. The spectrum analyzed here was obtained on a Perkin Elmer 1600 FTIR with 2 cm᎑1 resolution. HCl along with dry air was contained in a 10-cm gas cell at ambient pressure, with the concentration of HCl adjusted so that the maximum absorbance was around 1. Peak positions were determined by the spectrometer’s software. The only aspect of the spectrum considered here is analysis of the fundamental band to obtain the rotational and centrifugal distortion constants. (The method lends itself to analysis of peak intensities as well.) In the harmonic approximation, B and D may be determined graphically (1). However, if B′′ and B′ are both determined, the change in interatomic distance in the v = 1 state can be demonstrated. If analysis is restricted to levels with low values of J, then D may be disregarded and B′′ is obtained by subtracting two expressions of the form of eq 3, corresponding to the same value of B′ in the P and R branches, for example, P2 and R0. B′ is found from P1 and R1, for example (4 ). When D is to be determined as well, three or more such differences are generated. Students are inclined to consider this tedious. The method has the disadvantage of arbitrarily singling out for analysis only a few of the 20 or so peak positions available in the spectrum.
1188
The spreadsheet approach described below uses all of the data to obtain a best fit for the parameters ωe – 2ωexe (treated as a single variable here), B′′, B′, and D. What follows is an introduction to its use for students. A sample spreadsheet is shown in partial form. Using Solver To Find a Least-Squares Fit Linear regression is a familiar procedure, but when the theoretical equation to which data are expected to fit is not linearizable, there is no general way of finding the best values of the parameters. Iterative computer routines can be used to search for a good fit by a sort of systematic trial-and-error procedure (3, 5). One such routine is Solver in Excel. In the spreadsheet excerpt on the next page, columns A and B are the rotational quantum number assignments and column C shows the peak positions in cm᎑1 from an HCl spectrum. Column D contains a formula based on eq 3. The entry in cell D2, for example, is =$D$26+(A2*(A2+1))*$A$26-B2*(B2+1)*$B$26((A2*(A2+1))^2-(B2*(B2+1))^2)*$C$26
Of course, you don’t have to type this more than once; just copy down. Notice that the parameters in row 26 are used in the formula. The values shown are initial guesses. Reminder: the dollar signs indicate absolute addresses, which do not change when the cell is copied. Column E contains the square of the deviation between the experimental peak position and the calculated value in column D. The entry in cell E2 is =(C2-D2)^2 Cell E23 is the sum of the squared deviations, called the response. The entry in cell E23 is =sum(E2..E22) The response is a measure of goodness of fit and we want Solver to try to minimize it (least squares) by changing the values in cells A26 through D26. At this point, call Solver from the Formula menu. Tell it to minimize the response by varying the parameters: “Set cell $E$23 equal to 䉺MIN by changing cells $A$26:$D$26.” Hit Solve and watch cells E23 and A26–D26 as the Solver works. (If you graph columns C and D, then you can watch the graph change as well.) A problem with general fitting programs is that they can get lost and either not converge at all or converge at a “local minimum” of E23, which gives a poor fit. You may have to try again with different initial values of the parameters if the results do not appear reasonable.
Journal of Chemical Education • Vol. 75 No. 9 September 1998 • JChemEd.chem.wisc.edu
Information • Textbooks • Media • Resources Excerpt of Spreadsheet Used for Analysis of the HCl Infrared Spectrum A
B
C
D
E
1
J′
J′′
data
calc
(deviation)2
2
10
9
3057.2
3080
519.84
3
9
8
3042.8
3060
295.84
4
8
7
3027.9
3040
146.41
10
2
1
2923.9
2920
15.21
11
1
0
2904.2
2900
17.64
12
0
1
2862.9
2860
8.41
13
1
2
2841.6
2840
2.56
576
⯗
⯗ 20
8
9
2676
2700
21
9
10
2650
2680
900
22
10
11
2624.1
2660
1288.81
23
4551.3
24 25
B′
B′′
D
ωe-2ωexe
26
10
10
0
2880
27
Equation 3 can be fit analytically by a change of variables. However, this distances the spreadsheet from the raw data; also, the Solver method is more general. To my knowledge, there is no standard method for finding uncertainty intervals of the parameters determined by a nonlinear least-squares fit. One approach that has been used is to pick one parameter, add to it a small amount ε, and reoptimize the fit by varying only the remaining parameters. ε is changed by trial and error until the reoptimized response doubles, and the corresponding value of ε is reported as the uncertainty limit. The same procedure is repeated for negative values of ε, since the uncertainty intervals are not necessarily symmetrical. The other parameters are treated in turn. By this procedure, the data given here results in B′ = 10.118 ± 0.018 cm᎑1; B′′ = 10.419 ± 0.018 cm᎑1; D = (4.8 ± 1.1) × 10᎑4 cm᎑1, and ωe ᎑ 2ωexe = 2883.87 ± 0.14 cm᎑1. It is much easier to vary one parameter with the rest fixed until the response doubles using Excel’s Goal Seek function. However, the uncertainties obtained are smaller and have little statistical significance. Acknowledgment I wish to thank John S. Phillips for introducing me to the use of spreadsheets. Literature Cited
Comments Remarkably, after Solver has been run, the “response” (proportional to χ 2 if the data have equal weights) is reduced to 0.18 cm᎑2. The values returned for the parameters are B′ = 10.12 cm᎑1, B′′ = 10.42 cm᎑1; D = 5 × 10᎑4 cm᎑1 and ωe – 2ωexe = 2883.9 cm᎑1. Deviation of these values from established values (6 ) may be due to failure to resolve the H 37Cl contribution to the spectrum.
1. Shoemaker, D. P.; Garland, C. W.; Steinfeld, J. I.; Nibler, J. W. Experiments in Physical Chemistry; 3rd ed.; McGraw-Hill: New York, 1981; Experiment 42. 2. Walsh, S.; Diamond, D. Talanta 1995, 43, 561–572. 3. Machuca Herrera, J. J. Chem Educ. 1997, 74, 448–449. 4. Barrow, G. M. Introduction to Molecular Spectroscopy; McGrawHill: New York, 1962; Chapter 7. 5. Noggle, J. H. Physical Chemistry on a Microcomputer; Little, Brown: Boston, 1985. 6. Hollas, J. M. Modern Spectroscopy; Wiley: Chichester, 1987; Chapter 6.
JChemEd.chem.wisc.edu • Vol. 75 No. 9 September 1998 • Journal of Chemical Education
1189
