Excel statistical functions

We can use the Excel statistical functions to calculate the slope and intercept for a series of data, and the R value, without a plot. Open a new spreadsheet and enter the calibration data from Example 3.21, as in Figure 3.9, in cells A3 B7. In cell A9 type Intercept, in cell A10, Slope, and in cell All, R Highlight cell B9, click on / Statistical, and scroll down to INTERCEPT under Function name, and click OK. For Known x s, enter the array A3 A7, and for Known y s, enter B3 B7. Click... 

The standard deviation of the response can be determined based on the standard deviation of either the blank, the residual standard deviation of the least-squares regression line, or the standard deviation of the y intercept of the regression line. The Excel statistical function can be used to obtain the last two. 

As indicated, the values of Sr and S can be calculated from the Excel statistical functions (we don t have to calculate Syy needed for Sr, since Excel does that for us). The following spreadsheet is set up to perform the calculations. 

There are various reasons for replacing tabulated values by numerical approximations, chief among them to be able to automate the table look-up to save time and to present aspects that otherwise would go unnoticed. Commercial programs like Microsoft Excel feature many of the important statistical functions the file EXCEL FNC.xls that is provided with this manuscript shows how some functions are applied. The algorithms that are employed are very accurate, but not accessible as such. Eor the applications demonstrated in this work, appropriate approximations are incorporated into the VisualBasic programs that accompany the book. 

For common statistics, such as the Student s t value, chi-square, and Fisher F, Excel has functions that return the critical value at a given probability and degrees of freedom (e.g., =TINV (0.05,10) for the two-tailed Lvalue at a probability of 95% and 10 degrees of freedom), or which accept a calculated statistic and give the associated probability (e.g., =TDIST( t, 10, 2 ) for 10 degrees of freedom and two tails). Table 2.3 gives common statistics calculated in the course of laboratory quality control. 

Statistical functions are selected from two menus within Excel. The first approach is through the Function Wizard. Click the Function Wizard, fx to activate the Function Wizard dialog box select Statistical under the Function Category list and... 

Excel 2000 provides 78 statistical functions, including functions that return the mean, median, maximum and minimum values, average deviation, standard... 

A few of Excel s worksheet functions are array functions, that is, they return arrays as results. One of these, the LINES statistical function, is described in Chapter 11. 

Excel has a large number of matheinatical and statistical functions that can be used for calculations in lieu of writing your own formulas. Let s try the statistical functions to automatically calculate the mean. Highlight an empty cell and click fx on the tool bar. The Paste Function window appears. Select Statistical in the Function category. The following window appears ... 

Excel has numerous mathematical and statistical functions or syntaxes that can be used to simplify setting up calculations. Peruse the Function names for the Math Trig and the Statistical function categories underin the toolbar. Some you will find useful for this text are ... 

The LINEST program of Excel allows us to quickly obtain several statistical functions for a set of data, in particular, the slope and its standard deviation, the intercept and its standard deviation, the coefficient of determination, and the standard error of the estimate, besides others we will not discuss now. Linest will automatically calculate a total of 10 functions in 2 columns of the spreadsheet. 

Excel offers a number of statistical functions, listed under the Tools menu. Go to Add-Ins, and check Analysis ToolPak. Click OK and return to the spreadsheet. Now when you go to the Tools menu, you will see Data Analysis. Go to that, and you will see 19 statistical programs listed. As you experiment with these, you will find some very useful. One Add-In that is very useful is Solver, for solving complicated formulas. Its use is described in Chapter 6. See also the text website www.wiley. com/college/christian for a list of some commercial software packages for performing basic as well as more advanced statistical calculations. 

We will now introduce some new statistics that allow us to calculate the standard deviation (Sc) of the unknown concentration determined from the calibration curve, based on the number of measurements (N), the slope (m) of the cahbration curve, the average absorbance (yave)> the sum of the squares of deviations (S ) of the individual concentrations (jc,) from the mean of x (jCave)> and the standard deviation about regression (Sr). First review the use of the Excel LINEST statistical functions in Section 3.20. We will use some new syntaxes (statistical functions) to help perform the calculations (review useful syntaxes in Section 3.8). 

The time course of the complex formation was studied under pseudo-first-order conditions with excess ligand. Concentrations after mixing were at least 10 times larger than those of lead (II). Under such conditions the reaction proceeded to completion in a single rate-limiting step with no more initial loss of spectrophotometric amplitude than that expected from the dead time of the employed instrument. The process was found to be of first order with respect to lead(II). Indeed, the recorded signal could be fitted with excellent statistical confidence to a single-exponential function by nonlinear least squares. 

All excel functions output only one value at a time, though some can accept multiple cells at a time as input (mostly statistical functions). 

These are examples of useful statistics functions in Excel, they are not by any means the only ones. 

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 - xf" that we need for Equation 4-19. This sum is common enough that Excel has a built-in function called DEVSQ, which you can find in the Formulas ribbon under Insert Function in Statistical Functions. Formulas in Figure 4-9 are documented beside the cell where they are used. 

Table 8.2 lists some common statistical functions in Excel. Most of these functions as written only work on newer versions of Excel (2010 or newer). A detailed explanation of the functions and differences can be found in Sect. 2.4 Common Statistical Distributions. 

Numerical calculations and the layouts of the curves were all carried out using a traditional spreadsheet provided with its usual mathematical and statistical functions. The corresponding Excel sheets for each problem can be downloaded from the following website www.emse.fr/ soustelle. 

This is an indication of the collective nature of the effect. Although collisions between hard spheres are instantaneous the model itself is not binary. Very careful analysis of the free-path distribution has been undertaken in an excellent old work [74], It showed quite definite although small deviations from Poissonian statistics not only in solids, but also in a liquid hard-sphere system. The mean free-path X is used as a scaling length to make a dimensionless free-path distribution, Xp, as a function of a free-path length r/X. In the zero-density limit this is an ideal exponential function (Ap)o- In a one-dimensional system this is an exact result, i.e., Xp/(Xp)0 = 1 at any density. In two dimensions the dense-fluid scaled free-path distributions agree quite well with each other, but not so well with the zero-density scaled distribution, which is represented by a horizontal line (Fig. 1.21(a)). The maximum deviation is about... 

The behavior of a multi-particle system with a symmetric wave function differs markedly from the behavior of a system with an antisymmetric wave function. Particles with integral spin and therefore symmetric wave functions satisfy Bose-Einstein statistics and are called bosons, while particles with antisymmetric wave functions satisfy Fermi-Dirac statistics and are called fermions. Systems of " He atoms (helium-4) and of He atoms (helium-3) provide an excellent illustration. The " He atom is a boson with spin 0 because the spins of the two protons and the two neutrons in the nucleus and of the two electrons are paired. The He atom is a fermion with spin because the single neutron in the nucleus is unpaired. Because these two atoms obey different statistics, the thermodynamic and other macroscopic properties of liquid helium-4 and liquid helium-3 are dramatically different. 

It has been known for 40 years, since the initial PGSE experiment of Stejskal and Tanner [20], that NMR is an excellent tool for measuring the statistical displacements of molecules with time, usually called diffusion. The exponential attenuation a of the PGSE signal as a function of time between the PGSE pulses is given by [21]... 

It must be emphasized that the availability of the SMO and 2D autocovariance function methods as two independent statistical procedures to estimate the same parameter, in, the number of proteins, is a helpful tool to verify the reliability of the results obtained. In the case of the 2D PAGE map of colorectal adenocarcinoma cell line (DL-1) an excellent agreement was found between the values obtained from the SMO method—m = 101 10 and m = 105 10—and the 2D autocovariance function procedure—m = 104 10 (Pietrogrande et al., 2006a). 

---