A Spreadsheet for Least Squares

Cell B14 gives the number of data points with the formula = COUNT(B4 B7). Cell B15 computes the mean value of y. Cell B16 computes the sum S(q — )2 that we need for Equation 4-27. This sum is common enough that Excel has a built in function called DEVSQ that you can find in the Statistics menu of the INSERT FUNCTION menu. 

Highlight cells B10 C12 Type = LINEST(C4 C7, B4.B7,TRUE,TRUE) 

For PC, press CTRL+SHIFT+ENTER For Mac, press COMMAND+RETURN m 

Enter the measured mean value of y for replicate measurements of the unknown in cell B18. In cell B19, enter the number of replicate measurements of the unknown. Cell B20 computes the value of x corresponding to the measured mean value of v. Cell B21 uses Equation 4-27 to find the uncertainty (the standard deviation) in the value of x for the unknown. If you want a confidence interval for x, multiply sv times Student s t from Table 4-2 for n — 2 degrees of freedom and the desired confidence level. 

The results of many measurements of an experimental quantity follow a Gaussian distribution. The measured mean, x. approaches the true mean, p., as the number of measurements becomes very large. The broader the distribution, the greater is j. the standard deviation. For n measurements, an estimate of the standard deviation is given by s = V[2(x — x)2l/(n — 1). About two-thirds of all measurements lie within 1 a, and 95% lie within 2 j. The probability of observing a value within a certain interval is proportional to the area of that interval, given in Table 4-1. 

Highlight the 3-row X 2-column region B10 C12 with your mouse. On the Formulas ribbon, go to Insert Function. In the window that appears, go to Statistical 

We always want a graph to see if the calibration points lie on a straight line. Follow instructions in Section 3-6 to plot the calibration data. To add a straight line, click on one data point and they will all be highlighted. Go to the Chart Tools ribbon and select the Layout Tab. In the Analysis section, select Trendline and choose More Trendline Options. In the Format Trendline Window, select Linear and Display 

In Chart Tools, Layout, select Error Bars 

Click OK and error bars will appear on the chart 

---

Data analysis modern spreadsheets contain several convenient data-analysis aids, such as Excel s Trendline, a flexible linear least-squares tool, and Excel s Solver, a powerful multi-parameter non-linear least-squares fitting routine. Both of these are described in detail in chapter 3, and are used throughout the remainder of this book. Excel also contains a large number of tools for statistical data analysis. 

Note in Table 5.10 that many of the integrals are common to different kinetic models. This is specific to this reaction where all the stoichiometric coefficients are unity and the initial reaction mixture was equimolar. In other words, the change in the number of moles is the same for all components. Rather than determine the integrals analytically, they could have been determined numerically. Analytical integrals are simply more convenient if they can be obtained, especially if the model is to be fitted in a spreadsheet, rather than purpose-written software. The least squares fit varies the reaction rate constants to minimize the objective function ... 

Figure 7-12 Spreadsheet for efficient experimental design uses Excel UNEST routine to fit the function y = mAxA + nv% + /Tfcxfc to experimental data by a least-squares procedure.

An Excel spreadsheet illustrating the use of the Solver tool for nonlinear least-squares analysis of a fluorescent decay curve of a ruby crystal. The sum of the squares of residuals is calculated in cell C14 and is minimized in Solver by iterative variation of the parameters in cells CIO, Cll, and C12. 

As an extension of this exercise, modify your spreadsheet to include a column of residuals, as shown in Table 8-2. Create a plot of the residuals as a function of x. Residual plots can assist you in detecting any systematic deviations of the experimental points from the least-squares line. Be sure to save your spreadsheet in a file for reference and for use in analyzing laboratory data. 

Spreadsheet Summary Chapter 4 of Applications of Microsoft Excel in Analytical Chemistry introduces another way to perform a least-squares analysis. The Analysis ToolPak Regression tool has the advantage of producing a complete ANOVA table for the results. A chart of the fit and the residuals can be produced directly from the Regression window. An unknown concentration is found with the calibration curve, and a statistical analysis is used to find the standard deviation of the concentration. 

Spreadsheet Summary In the first exercise in Chapter 12 of Applications of Microsoft Excel in Analytical Chemistry, a spreadsheet is developed to calculate the molar absorptivity of permanganate ion. A plot of absorbance versus permanganate concentration is constructed, and least-squares analysis of the linear plot is carried out. The data are analyzed statistically to determine the uncertainty of the molar absorptivity. In addition, other spreadsheets are presented for calibration in quantitative spectrophotometric experiments and for calculation of concentrations of unknown solutions. 

Spreadsheet Summary In Chapter 12 ot Applications of Microsoft Excel in Analytical Chemistry, we investigate the multiple standard additions method for determining solution concentration. A least-squares analysis of the data leads to the determination of the concentration of the analyte as well as the uncertainty of the measured concentration. 

S Spreadsheet Summary In the final exercise of Chapter 13 of Appli-"II cations of Microsoft Excel in Analytical Chemistry, the initial-rate method is explored for determining the concentration of an analyte. Initial rates are determined from a linear least-squares analysis and are used to establish a calibration curve and equation. An unknown concentration is determined. 

Most determination methods finally lead to discrete loading versus concentration data that have to be fitted to a continuous isotherm equation. For this purpose it is advised to use a least-squares method to obtain the parameters of the isotherm. Nonlinear optimization algorithms for such problems are implemented in standard spreadsheet programs. To select an isotherm equation and obtain a meaningful fit,... 

The general least-squares procedures can now be implemented in spreadsheets programmed with macros. Adjustments once impossible are now trivial. The classification of molecules to obtain electron affinities from half-wave reduction potentials is an example of a linear least-squares adjustment. The determination of the adiabatic electron affinity for acetophenone is an example of a nonlinear two-parameter least-squares procedure. The nonlinear least-squares adjustment of ECD to the expanded kinetic model is one of the major advances of the 1990s. 

Other problems with this simple approach are that estimates of the parameters obtained from other experiments or which are indicated by theory cannot be included in the adjustment. For example, if the intercept has been determined by a series of other experiments to be a , v , it is appropriate to include that value in the adjustment of new data to estimate the slope b. Alternatively, if the slope b is 1.0 0.05, then this should be included in the adjustment to obtain a more precise value of a. Also, the quality of the fit of the data to the function is often not realistic because of the assumptions concerning the random errors. Finally, it is difficult to consider additional variables and/or parameters or nonlinear functions. Thus, the use of a general least-squares operation is possibly more valuable than this simple approach and with modem spreadsheets is no more difficult. 

---