Spreadsheet average

The example spreadsheet covers a three-day test. Tests over a period of days provide an opportunity to ensure that the tower operated at steady state for a period of time. Three sets of compositions were measured, recorded, normalized, and averaged. The daily compositions can be compared graphically to the averages to show drift. Scatter-diagram graphs, such as those in the reconciliation section, are developed for this analysis. If no drift is identified, the scatter in the measurements with time can give an estimate of the random error (measurement and fluc tuations) in the measurements. 

A simple linear regression is very easily computed with an ordinary computer spreadsheet program such as Lotus 1-2-3 or Microsoft Excel. The method involves calculating first the average values of x and y, then the values of Co and Ci, and then the estimated value of y ... 

The next step is to examine the remaining data to see if a laboratory average or pair are too extreme in comparison with the rest of the data. This is somewhat more complicated and involves the calculation of Grubbs statistics but can be easily done with a spreadsheet. The process is as follows ... 

Fig. 4.10 Instantaneous nondimensional velocity profiles in a circular duct with an oscillating pressure gradient. The mean velocity, averaged over one full period, shows that the parabolic velocity profile or the Hagen-Poiseuille flow. These solutions were computed in a spreadsheet with an explicit finite-volume method using 16 equally spaced radial nodes and 200 time steps per period. The plotted solution is that obtained after 10 periods of oscillation.

Fig. D.5 The mesh network to solve the momentum equation for the axial velocity distribution in a rectangular channel. As illustrated, the control volumes are square. However, the spreadsheet is programmed to permit different values for dx and dy. Because of the symmetry in this problem, only one quadrant of the system is modeled. The upper and left-hand boundary are the solid walls, where a zero-velocity boundary condition is imposed. The lower and right-hand boundaries are symmetry boundaries, where special momentum balance equations are developed to represent the symmetry. As illustrated, there is an 12 x 12 node network corresponding to a 10 x 10 interior system of control volumes (illustrated as shaded boxes). The velocity at the nodes represents the average value of the velocity in the surrounding control volume. There are half-size control volumes along the boundaries, with the corresponding velocities represented by the boundary values. There is a quarter-size control volume in the lower-left-hand corner.

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. 

For each decay time measurement, 256 or more measurements should be averaged and the decay curve transferred to a computer. The means to accomplish this transfer can be provided by software from the oscilloscope manufacturer, LabVIEW, or a custom program developed for this purpose. Once the decay curve data are available, they may be imported into a spreadsheet for determination of k using... 

Raw data were received by an independent collaborator, identifying marks removed and the anonymous results forwarded to the 1994 ABRF Amino Acid Analysis Research Committee as a spreadsheet. Data reduction was as described [2], except that the yields of the individual amino acids were not rounded to the nearest integer value. The amount of sample (pmol) was calculated from its known content of amino acids, excluding Cys and values differing >15% from the average. The accuracy of each residue was calculated as % Error, and the overall accuracy of the composition as Average % Error, where... 

In this exercise, we have learned to calculate a mean, using both the built-in Excel AVERAGE function and a formula of our own design. In Chapter 6, we will use STDEV and other functions to complete our analysis of the data from the gravimetric determination of chloride that we began in Chapter 2. You may now close Excel by typing File/Exit or proceed to Chapter 6 to continue with the spreadsheet exercises. 

If you are continuing the Spreadsheet Exercise from Chapter 5, begin with the data on your computer screen. Otherwise, retrieve the file average.xls from your disk by clicking on File/Open. Make cell D1 the active cell, and type... 

Figure 1 9-4 Spreadsheet and plot for titration of 50.00 mL of 0.0500 M Fe " with 0.1000 M Ce. Prior to the equivalence point, the system potential is calculated from the and Fe + concentrations. After the equivalence point, the Ce and Ce + concentrations are used in the Nernst equation. The Fe concentration in cell B7 is calculated from the number of millimoles of Ce added, divided by the total volume of solution. The formula used for the first volume is shown in documentation cell A21. In cell Cl, [Fe- ] is calculated as the initial number of millimoles of Fe present, minus the number of millimoles of Fe formed, divided by the total solution volume. Documentation cell A22 gives the formula for the 5.00-mL volume. The system potential prior to the equivalence point is calculated in cells F7 F12 by using the Nernst equation, expressed for the first volume by the formula shown in documentation cell A23. In cell F13, the equivalence-point potential is found from the average of the two formal potentials, as shown in documentation cell A24. After the equivalence point, the Ce(lll) concentration (cell D14) is found from the number of millimoles of Fe- initially present divided by the total solution volume, as shown for the 25.10-mL volume by the formula in documentation cell D21. The Ce(IV) concentration (El 4) is found from the total number of millimoles of Ce(lV) added, minus the number of millimoles of Fe + initially present, divided by the total solution volume, as shown in documentation cell D22. The system potential in cell FI4 is found from the Nernst equation as shown in documentation cell D23. The chart is then the resulting titration curve.

Some researchers, of medical scientists or biostatisticians, have maintained that the same record could be used for all purposes it is merely a matter of what the researcher is allowed to access. There seems to be a general feeling that an ARC should be a distinct archive outside the security firewall, with the identified records held inside. Moreover there are subtleties in statistics that allow certain statistical overviews to nail down detailed data. For example, if a spreadsheet, contains multidimensional data with only averages in the rows and columns, it should not be possible for anyone to work back from the marginal summaries to deduce the interior of the spreadsheet. But in fact a mathematical algorithm is already known to medicine that allows medical images to be constructed—tomography. 

Fig. 2.5 The spreadsheet used to calculate the weighted average age of an ancient wooden beam dating from about 20.6 2.3 centuries BC.

In this chapter we will encounter a number of standard mathematical operations that are conveniently performed and/or illustrated on a spreadsheet. We start with a brief description of the logic underlying the Goal Seek and Solver methods of Excel. Then we consider two methods often encountered in spectroscopy, viz. signal averaging and lock-in amplification. Subsequently the focus shifts toward numerical methods, such as peak fitting, integration, differentiation, and interpolation, some of which we have already encountered in one form or another in the context of least squares analysis and/or Fourier transformation. Finally we describe some matrix operations that are easy to perform with Excel. 

Calculate the value of t, t = ( vassigned — x /n)/s, using = ABS (assigned value — AVERAGE) range)) SQRT(n)/STDEV (range). For the data in spreadsheet 3.4 this t-value is 3.334. 

First calculate the grand mean which is the mean of the all the data points. Therefore in Excel the overall mean is = AVERAGE(rangie) which for the above data included in spreadsheet 4.1 is = AVERAGE(A2 C7) giving the value 1.757. 

---