Standard deviation spreadsheet

Validation also applies to software. In a simple example, you could create an Excel spreadsheet template with fixed formulae to calculate the mean and standard deviation of a range of data. To validate this template you would enter a set of sample data and verify the template-calculated results against the manually calculated results. In order to be confident that the template could be used for further data sets you would password-protect the cell formulae and verify that they cannot be altered without it. 

As may be seen in the example in spreadsheet 2.1, the robust estimate of standard deviation (MAD/0.6745) agrees with standard deviation of the amended data. 

Spreadsheet 2.1. Calculation of mean, standard deviation, median, and mean absolute deviation of titration data with outlier, and after correction (shaded cells show the change from 11.02 to 10.02). 

Following are some data from an Australian soil trial in which a sample of soil was analyzed three times by each of 10 laboratories for TP A (total peroxide acidity expressed as moles per tonne). The results are given in spreadsheet 2.3. The sample variance is calculated as the square of the sample standard deviation, and the Cochran statistic is calculated from equation 2.20. 

Figure 4.19. Shewhart means chart of the means of duplicate data of research octane number in spreadsheet 4.2. The target mean was 95 and the standard deviation was 2.1.

The manufacturer s calibration information must now be accounted for. You might be lucky and have a 10-mL pipette that is indeed 10.00 mL, but the manufacturer is only guarantees the pipette to be not less than 9.98 mL and not more than 10.02 mL. As the pipette is used throughout many experiments, then this possible systematic effect will not dissipate with repeated measurements and must be accounted for. There are two alternatives. The first is to calibrate the pipette in the laboratory. The fill-and-weigh experiments that would give the standard deviation of the measurement will also, from the mean, give an estimate of the volume of the pipette (see spreadsheet 6.1). 

The final combined standard uncertainty, whether obtained using algebra or a spreadsheet or other software, is the answer, and can be quoted as such. I recommend using the wording Result x units [with a] standard uncertainty [of] uc units [where standard uncertainty is as defined in the International Vocabulary of Basic and General Terms in Metrology, 3rd edition, 2007, ISO, Geneva, and corresponds to one standard deviation]. ... 

Calculators and spreadsheets include the following function to calculate standard deviation (for an infinite number of measurements, s is replaced by a, x is replaced by p and n — 1 is replaced by n) ... 

The response of many instruments is linear as a function of the measured variable, if variations due to experimental conditions or the instrument are taken into account. The objective is to determine the parameters of the linear equation that best represents the observations. The primary hypothesis in using the method of least squares is that one of the two variables should be without error while the second one is subject to random errors. This is the most frequently applied method. The coefficients a and b of the linear equation y = ax + b, as well as the standard deviation on a and on the estimation of y have been obtained in the past using a variety of similar equations. The choice of which formula to use depended on whether calculations were carried out manually, with calculator or using a spreadsheet. However, appropriate computer software is now widely used. 

In a spreadsheet, enter the mean in cell A2 and the standard deviation in cell B2. To find the area under the Gaussian curve from — oo to 900 h in cell C4, we select cell C4 and go to the INSERT menu and choose FUNCTION. In the window that appears, select the Statistical functions and find NORMDIST from the list of possibilities. Double click on NORMDIST and another window appears asking for four values that will be used by NORMDIST. (If you click on help, you will find a cryptic explanation of how to use NORMDIST.)... 

Excel has built-in procedures for conducting tests with Student s t. To compare Rayleigh s two sets of results in Table 4-3, enter his data in columns B and C of a spreadsheet (Figure 4-8). In rows 13 and 14. we computed the averages and standard deviations, but we did not need to do this. 

The method of least squares is used to determine the equation of the best straight line through experimental data points. Equations 4-16 to 4-18 and 4-20 to 4-22 provide the least-squares slope and intercept and their standard deviations. Equation 4-27 estimates the uncertainty in x from a measured value of y with a calibration curve. A spreadsheet greatly simplifies least-squares calculations. 

B. illii Spreadsheet for standard deviation. Let s create a spreadsheet to compute the mean and standard deviation of a column of numbers in two different ways. The spreadsheet below is a template for this exercise. 

Bill Excel LINEST function. Enter the data from Problem 4-23 in a spreadsheet and use the LINEST function to find the slope and intercept and standard deviations. Use Excel to draw a graph of the data and add a TRENDLINE. 

A more efficient experimental design provides concentrations and standard deviations in fewer than nine experiments. One of many efficient designs is shown in Figure 7-12. Instead of titrating each acid by itself, we titrate mixtures of the acids. For example, in row 5 of the spreadsheet, a mixture containing 2 mL A, 2 mL B, and 2 mL C required 23.29 mL of 0.120 4 M NaOH, which amounts to 2.804 mmol of OH. In row 6, the acid mixture contained 2 mL A, 3 mL B, and 1 mL C. Other permutations are titrated in rows 7 and 8. Then row 5 is repeated independently in row 9. Column E gives mmol of base for each run. 

The mathematical relationships using the differential method to determine a, P and k are linear, and any commercial spreadsheet or scientific software package will have a suitable fitting program. It is desirable that the fitting software gives the standard deviations of the fitted parameters, so that the statistical errors of reaction orders and rate coefficients are known. 

Follow we discuss the implementation of the %2 analysis in an Excel spreadsheet. It deals with the emission decay of a solution with two emitters of slightly different lifetimes. Measurements are done at one wavelength only. Column C of the Excel spreadsheet shown in Figure 7.10 contains the estimated standard deviation standard deviation is simply the square root of the intensity. Column D contains the calculated intensity as the sum of two exponential decays. 

Another advantage in knowing the standard deviations of the measurements is that we can determine if a fit is sufficiently good. As a rule of thumb, this is achieved if X2 = v, where v is the degree of freedom, which has earlier been defined in Equation 7.23. With x1 = 72.5 and v = 96 (100 - 2 - 2), this condition is clearly satisfied for our example spreadsheet in Figure 7.10. If X2 is too big, something is wrong, most likely with the model. If X2 is too small, most likely the have been overestimated. 

These numbers can be obtained using simple functions, e.g. in a spreadsheet, but are often conventionally presented in tabular form. There are a surprisingly large number of types of tables, but Table A.l allows the reader to calculate relevant information. This table is of the cumulative normal distribution, and represents the area to the left of the curve for a specified number of standard deviations from the mean. The number of standard deviations equals the sum of the left-hand column and the top row, so, for example, the area for 1.17 standard deviations equals 0.87900. 

If unit weights are employed, aU Wj are deleted and S Wj is replaced by m.) These equations may be useful when a simple straight-line fit ( linear regression ) is being done with a spreadsheet program or a pocket calculator. Many calculators accumulate most or aU of the sums required in Eqs. (20) to (22) some complete the calculation and offer both the refined parameters and their estimated standard deviations. 

Figure 5-13. Spreadsheet layout, with standard deviations, for a chart with error bars.

Figure 5-13 shows a spreadsheet containing kinetic data for an acid-catalyzed reaction, together with standard deviations of the fcobsd values. 

In this exercise, we will calculate the standard deviation, the variance, and the relative standard deviation of two sets of data. We begin with the spreadsheet and data from the Spreadsheet Exercise in Chapter 5. The standard deviation is given by the equation... 

As a final exercise, retrieve the spreadsheet that we created in Chapter 3 for the gravimetric determination of chloride, which we called grav chloride.xls. Enter formulas into cells B12—B14 to compute the mean, standard deviation, and the RSD in parts per thousand of the percent chloride in the samples. In this example, multiply the relative standard deviation by 1000 in cell B14. Adjust the decimal point in the results to display the proper number of significant figures. The worksheet below shows the results. Save your worksheet so that you can use it as a model for making laboratory calculations. 

Spreadsheet Summary In Chapter 2 oi Applications of Microsoft Excel in Analytical Chemistry, we introduce the use of Excel s Analysis ToolPak to compute the mean, standard deviation, and other quantities. In addition, the Descriptive Statistics package finds the standard error of the mean, the median, the range, the maximum and minimum values, and parameters that reflect the symmetry of the data set. 

Spreadsheet Summary In Chapter 2 of Applications of Microsoft Excel in Analytical Chemistiy, we develop a worksheet to calculate the pooled standard deviation of the data from Example 6-2. The Excel function DEVSQO is introduced to find the sum of the squares of the deviations. As extensions of this exercise, you may use the worksheet to solve some of the pooled standard deviation problems at the end of this chapter. You can also expand the worksheet to accommodate more data points within data sets and larger numbers of sets. 

In this equation, the number of degrees of freedom is iV — 2, since one degree of freedom is lost in calculating m and one in determining b. The standard deviation about regression is often called the standard error of the estimate. It roughly coiresponds to the size of a typical deviation from the estimated regression line. Examples 8-4 and 8-5 illustrate how these quantities are calculated and used. The spreadsheet calculation of these quantities is illustrated in the Spreadsheet Exercise later in this section. 

---