Spreadsheet Data Tools

Once entered into a spreadsheet, data can be manipulated column at a time. For example, let us take the top cells in Table 1-3 as cells A3 and B3 (columns A and B, line 3 in Table 1-3) containing 5 and 0.305 to avoid dividing 0 by 0. Using the easycalc option of the tools menu in Excel, divide the contents of B3 by A3 and place the results in cell C3. Now select C3 and the remaining 12 unfilled cells in the column, C3 to Cl 5, and fill down using the mouse. The results of the calculation of Cp/T appear for all remaining cells in the C column. 

Single-axis accelerometer systems are used in the steering tools and MWD tools for inclination and tool face data acquisition. Using a spreadsheet, compute the current values for each accelerometer in the following cases ... 

For reasons discussed above, we needed a complementary, ancillary tool for comparison of the mass spectra of components from multiple urine samples. We desired that the procedure have several characteristics (1) requires little if any manual data entry by the operator (2) utilizes data automatically generated by ChemStation and organized into Microsoft Excel spreadsheets (3) displays both retention times and mass spectral data in the same window (4) minimizes subjective operator judgments and (5) is simple and rapid to use. What emerged after several iterative improvements are the FindPeak macros discussed below. These are largely due to the expertise of Y. Aubut, with valuable input from J. Eggert. 

A simple spreadsheet tool can be used to capture the results of the fraction analyses, provide data visualization, integrate the responses, and thereby establish starting points for the fraction collection window(s), estimation of target purity and amount recovered. This data is useful for extrapolating the number of... 

Finally, the Add Ins. .. of the Data Analysis Tools gives the three methods (paired, means with equal variance, and means with unequal variance) as menu-driven options (see spreadsheet 2,4), Access Data Analysis. . . from the Tools menu (if it is not there, the Analysis ToolPak needs to be installed via the Add-Ins. . . menu, also found in the Tools menu). 

In Excel, there are three options for ANOVA one factor, two factor without replication, and two factor with replication. The different options in the Data Analysis Tools menu are shown in spreadsheet 2,6,... 

Spreadsheet 2.4. (a) The Add-In menu, (b) The Data Analysis Tools menu showing the three t test options, (cj Menu for the t test assuming equal variances. 

Since Excel is a powerful tool used widely for many different purposes with many options not all options can be discussed in this chapter. The focus of the chapter is on manually self-created spreadsheets for data calculation and checks against acceptance criteria (logical operations). Excel spreadsheets that are used with other electronic systems for automatic data or information entry, for further operations, or used as a view tool for databases are not within the scope of this chapter. Nevertheless, these types of spreadsheets are viewed as a normal spreadsheet with automatic entry, and validation, including validation of the interface, will be included to cover this item. In this chapter we provide guidance in validation and revalidation of Excel spreadsheets and information about managing validated spreadsheets. 

Spreadsheets can be a powerful tool to perform calculations and data evaluation more efficiently and to reduce errors in a cGXP environment. However, the possible problems and resources needed to develop, validate, and revalidate spreadsheets should not be underestimated. 

HI Now we use a built-in routine in Excel for the paired t test to see if the two methods in Problem 4-14 produce significantly different results. Enter the data for Methods 1 and 2 into two columns of a spreadsheet. Under the TOOLS menu, select DATA ANALYSIS. If DATA ANALYSIS does not appear, select ADD-INS. Select ANALYSIS TOOLPACK, click OK, and DATA ANALYSIS will be loaded into the tools menu. In the DATA ANALYSIS window, select t-Test Paired Two Sample for Means. Follow the instructions of Section 4-5 and the routine will print out a variety of information including tcafcu ated (which is labeled t Stat) and ftable (which is labeled t Critical two-tail). You should reproduce the results of Problem 4-14. 

In order to evaluate results obtained from different calculation runs, a number of standardized reports were integrated into the decision support tool. The reports can be clustered into the three categories production network structure, cost structure and investment/restructuring activities (cf. Fig. 49). Since reports become very complex if they contain data from several scenarios, comparative analyses of different scenarios are performed via data downloads into MS Excel pivot spreadsheets. 

LINEST is a function that is included in almost every spreadsheet software, including Microsoft Excel, OpenOffice.org Calc, and Google Docs Spreadsheet. LINEST accepts a table of values for a dependent variable (experimental activity) and any number of independent variables (such as parameters for use in a Hansch equation). LINEST then outputs the best-fit coefficients for the independent variables and certain statistical parameters for the regression. While Excel s Regression option in the Data Analysis tool is more user friendly, LINEST is much more widely available. 

Linear and nonlinear programming solvers have been interfaced to spreadsheet software for desktop computers. The spreadsheet has become a popular user interface for entering and manipulating numeric data. Spreadsheet software increasingly incorporates analytic tools that are accessible from the spreadsheet interface and permit access to external databases. For example, Microsoft Excel incorporates an optimization-based routine called Solver that operates on the values and formulas of a spreadsheet model. Current versions (4.0 and later) include LP and NLP solvers and mixed integer programming (MIP) capability for both linear and nonlinear problems. The user specifies a set of cell addresses to be independently adjusted (the decision variables), a set of formula cells whose values are to be constrained (the constraints), and a formula cell designated as the optimization objective. 

One of the most useful tools to spot and eliminate errors is a spreadsheet, such as Excel or QuattroPro. QSAR modelers very frequently use spreadsheets to organize data into columns and rows of standardized values of the independent and dependent parameters. Spreadsheets allow easy sorting and filtering — two important functions used to find problem data and duplicates and other errors. In addition, spreadsheets have search and replace routines, plotting, and correlation functions, which allow the data to be reviewed in various comprehensive ways. The data can also be exported to other file types, which allow analysis by other software for statistics and any types of quantitative and qualitative relationships that may exist. It cannot be emphasized enough that the typical spreadsheet functions (including graphing functions) are excellent tools to find and eliminate erroneous or questionable values, duplicates, and other problem entries. 

To perform the analysis, enter the data into an Excel spreadsheet (start at the top left-hand comer cell Al), then select the ANOVA Single Factor option from the Tool Menu. Select all the data by entering B 2 E 6 in the input range box (or select the data using the mouse). Now ensure that you select the Grouped By Rows Radio Button, as the default is to assume the data are grouped in columns (remember we want to... 

Excel, a very powerful tool for many statistical calculations, is widely available. The routine use of a spreadsheet will dramatically reduce any errors due to incorrect calculations performed by hand as the data you are using are always visible on screen and so any errors are easily spotted. Also, saving the workbooks allows one to review any calculations over time to ensure no errors have occurred. 

---