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. 

---

Figure 4-9 uses built-in power of Excel for least-squares calculations of straight lines. As an example, enter the x- and y-coordinates from Table 4-7 into cells B4 through B7 and C4 through Cl. This range is abbreviated B4 C7. The key portion of the spreadsheet uses the Excel function FINEST to compute the least-squares parameters in cells B10 C12. 

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 4-13 Spreadsheet for linear least-squares analysis.

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. 

---