1APPENDIX#3: Mircea D. Gheorghiu Non-linear Curve fitting with Microsoft Excel Solver and Non-Linear Regression Statistics1 A. CALCULATION OF KOBS, KREAL AND DEBYE-HÜCKEL PLOT. A1. Kinetics calculation: kobs, kreal………………..page 1 A1.1 “The crude” step…………………………………page 7 A1.2 Optimization step ……………………………..page 9 A2. Debye-Hückel plot……………………………………page 18 B. NON-LINEAR STATISTICS…………………………………………...page 23 A. Calculation of kobs, kreal and Debye-Hückel plot. A1. Kinetics: calculation of kobs and kreal. 1. From File click on New.., then on General Workbook: 1 E. J. Billo “Excel® for Chemists”, 2nd ed., Wiley: New York, 2001, Chapiter 12. For questions you can contact Prof. Billo (Dept. Chem., Boston College, Chestnut Hill, MA): [email protected]; see also: http://chemserv.bc.edu/faculty/billo.html2 2. From File, Save as… the workbook. My preference for file name is Kinetics_MG (MG are my initials) and it is saved in the Personal folder.3 3. Five sheets are necessary in your workbook. Four are for Kinetics data. The fifth is for Debye_Huckel calculation. You have to append these five sheets to your written or Oral report. If there are not enough Sheets, click Insert and then Worksheet. Name four sheets, each for a kinetic run. For example, I have chosen: KineticsA (for the 0.02M NaNO3), KineticsB (for the 0.05M NaNO3), KineticsC, (for the 0.1M NaNO3) and KineticsD (for the 0.2M NaNO3).4 4. Now is time to add your experimental data to the four sheets. Take for example, KineticsB sheet. Type in column A, the time (in seconds), and in column B the experimental absorbances (@420nm) corresponding to the respective time. Add on the Table two more columns. One for calculated absorbances (from equation 3) and a second column for the square of the difference experimental absorbances (column B)-calculated absorbances (column C).55. Just as a reminder, the second order integrated kinetic equation, as it was presented to you in my hand-out, is printed next. The meaning of variables are the same as in the hand-out and the Lab Manual: )tkcexp(AAA11AAobsfff---=00(3)Integrated second order kinetic equation in terms of absorbance that is curve-fitted to the experimental data.A0= initial absorbanceA = absorbance at time tAf = absorbance when all H2Asc has reacted. 6. We are now just a step before using Microsoft Excel Solver. On each kinetic sheet one must add cells containing two sets of information. In cells H2 and H3 are typed the values of the “fixed” variables A0 and epsilon, respectively. The content of the cells H5 (Af value) and H6 (kobs) is changing. Initially, “guess” values are typed in for the variables of Af and kobs. After the minimization process, Solver returns the regression coefficients in the changing cells H5 and H6, respectively. Solver is not providing the standard deviations of the coefficients; see B section. 7. In order to be automatically plugged into the kinetic equation, the cells containing the values of A0, epsilon, Af, kobs must be given a name (this is an Excel requirement). · For A0, type =B2 in cell H2 · For epsilon type in cell H3, the value obtained by you for epsilon (calculated from Lambert-Beer equation, recorded during day #1). The slope of the least square straight line, calculated from my results, gave e = 1020. · Type in cell H5 the best guess value for Af, that is 0.25 (Why?). · Type the your guess value for kobs in cell H6. My guess is 5.6In order to be automatically inserted in equation 3, A0, epsilon, Af and kobs must be assigned a name. For example to name A0, first click on cell H2. Than click on Insert, Name, Define: The following window pops-up: Please notice and check the correct location of the value of A0, in this case is (according to Excel grammar): KineticsB!$H$2, that is on KineticsB sheet and location H2. Click on add button. Click on OK. The naming continues for cells H3:H5. Next, let us name as t the vector A2:A22. First highlight the column A2:A22, then click on Insert, Name, Define and change the names in workbook as t (check Refers to address in order be correct). The Define Name window will look like:7 8. Solver optimizes the curve fitting in two steps: · In the first step, “crude” values of absorbances are calculated. · In the second step, the optimization step, the crude values of calculated absorbances are refined to best fit to experimental values. A. The “Crude” Step: Type in cell C2 =Af/((1-((A0-Af)/A0)*EXP(-kobs*t*Af/epsilon))). Cell H2 is filled with the calculated absorbance for t=0 seconds. According to equation 3 it is equal with A0.8 In order to fill in cells C3 through C22, click on cell C2. Bring the cursor to the right low corner and press left mouse. Drag all the way down to cell C22. Depress the left mouse. All cells (C2:C22) are now filled in with the calculated (“crude”) Absorbances:9A2. Optimization step: Non-linear curve fitting step. 9. Type in cell D2=(B2-C2)^2. Press Enter key. 10. Click on cell D2. Drag all the way down to cell D22, as it was described for calculated absorbances. 11. In cell D23 sum (click on icon S) D2 through D22. Than press Enter key. 12. Click on cell D23. Click Tools and than Solver…10 The Solver Parameters window pops-up. The target cell is D23. Type into By Changing the cells: H5 and H6 (that is $H$5 and $H$6).11 On Solver Parameters click on Options. Change Max Time to 1200 seconds (kinetics run time). Click OK. The Solver Parameters window comes back. Click first on Min and than on Solve button:12 The Solver Results window pops-up. Note that the values in cells H4 and H5 are updated. You know by now the value of kobs as 2.60. Note that the initial value the “guess”} has been taken as 5. You can print some reports: answer, sensitivity and limits. For Example the Answer Report looks like:13 Repeat steps 4 through 12 for sheets KineticsA, KineticsC and KineticsD. Whenever is necessary, please update the Reference in the Define Name window. A3. Debye-Hückel equation. In the “Kinetics” hand-out (see there the meanings of variables), the Debye-Hückel equation is defined as: 212102121210130211////realII**.klogIIZ*Z*1.02klogklog++=++= (6) where kreal is given by equation (4):