Excel Data Analysis Tools

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,... 

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. 

The one thing at which Excel does not excel is statistical analysis. The (very basic) level of material covered in this chapter is about at (if not beyond) the limit of its capabilities. It can be used to generate means, medians, SDs and quartiles, but while the first three are OK, the quartile values generated are somewhat unconventional and will not be pursued further. The mean, median and SD of a data set can be generated by using either worksheet functions or the Data Analysis tool. 

However, computers are quicker (and more reliable). Excel does not offer the SEM as a standard worksheet function, but it is included in the output from the Data Analysis tool (see Chapter 2). Both Minitab and SPSS include it in their Descriptive Statistics routines. 

With most packages you will simply enter the data into a column, call up the appropriate routine and identify the column containing the data. You will then be supplied with a point estimate for the mean and the upper and lower limits of the confidence interval. Good old Excel and its Data Analysis tool half does the job it provides a figure that needs to be added to/subtracted from the mean to obtain the limits of the interval. 

Linear least-squares analysis is quite easy with Excel. This type of analysis can be accomplished in several ways by using the equations presented in this chapter, by employing the basic built-in functions of Excel, or by using the regression data analysis tool. Because the built-in functions are the easiest of these options, we explore them in detail here and see how they may be used to evaluate analytical data. 

Results from use of Data Analysis tool in EXCEL ... 

Our near future improvements include automatic defect detection and reporting, and integration of reports into Word or Excel documents. On the long nin, automatic comparison with historical data will be possible, and if acquisition cards improve, acquisition may be perfomied by the analysis tools. 

Place the cursor in the A1 cell. Under tools select data analysis, highlight regression, and click OK. (Note If data analysis is not active in your Excel program, go to help for instructions on how to load it.)... 

The HCA technique examines the interpoint distances between the samples in a data set and represents that information in the form of a two-dimensional plot called a dendrogram. The HCA method is an excellent tool for preliminary data analysis. It is useful for examining data sets for expected or unexpected clusters, including the presence of outliers. It is informative to examine the dendrogram in conjunction with PCA because they give similar information in different forms. 

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. 

Large-scale gene expression studies, especially microarray technologies, have rapidly become a significant part of bioinformatics. There are many excellent sites focusing on microarray databases and tools for data analysis. The following sites are simply some examples. 

Excel provides some built-in tools for fitting models to data sets. By far the most common routine method for experimental data analysis is linear regression, from which the best-fit model is obtained by minimizing the least-squares error between the y-test data and an array of predicted y data calculated according to a linear... 

Figure 2.3. Linear regression analysis with Excel. Simple linear regression analysis is performed with Excel using Tools -> Data Analysis -> Regression. The output is reorganized to show regression statistics, ANOVA residual plot and line fit plot (standard error in coefficients and a listing of the residues are not shown here).

Because of the limited space we focus on a user-oriented description of basic aspects of principal component analysis (PCA). PCA is an excellent tool for exploratory data analysis in chemistry. A number of surveys on the subject have already been published and it is strongly recommended to refer to a selection of them (ref. 1-7). 

One can also use the Data Analysis Toolbox feature of Microsoft Excel. If this feature does not appear in the Tool menu, you will need to install it. To perform the same test, select ToolsXData Analysis Toolbox t test Paired two Sample for Means. 

The ANOVA calculation can be performed in Excel only if the Data Analysis Toolbox has been installed. To perform the calculation, you can select the ANOVA tool under ToolsVData Analysis ANOVA Single Factor from the Excel toolbar. Using the following example of arsenic content of coal taken from different parts of a ship s hold, where there are five sampling points and four aliquots or specimens taken at each point, we have the data as shown below ... 

If properly installed, there should be a Data Analysis item in the Tools menu. If this does not appear you should select the Add-ins option and the tick tire Analysis Toolpak . Normally this is sufficient, but sometimes the original Office disk is required. One difficulty is that some institutes use Excel over a network. The problem with this is that it is not always possible to install these facilities on an individual computer, and this must be performed by the Network administrator. 

VANTED, short for visualization and analysis of networks with related experimental data, uses networks produced by the software tool itself or derived from the KEGG database. It allows representation of transcript, enzyme, and/or metabolite data on the networks (e.g., for time-series data). A standardized Excel sheet serves as input for the application. It offers advanced data analysis methods, such as correlation analysis or selforganizing maps (30,31). 

The power of the Excel program lies in its tools. Goal Seek and Solver were discussed Chapters 2-4, and Data Analysis is discussed in Appendix E. Other useful tools include Tools/Protection (to prevent part or all of the spreadsheet from being changed) and Tools/Flag for Follow Up (to notify you on some future date when you may need to update the spreadsheet). 

Data analysis modern spreadsheets contain several convenient data-analysis aids, such as Excel s Trendline, a flexible linear least-squares tool, and Excel s Solver, a powerful multi-parameter non-linear least-squares fitting routine. Both of these are described in detail in chapter 3, and are used throughout the remainder of this book. Excel also contains a large number of tools for statistical data analysis. 

The Analysis Toolpak of Excel already includes a Fourier transform function, available through Tools => Data Analysis => Fourier Analysis. Unfortunately that function suffers from three serious limitations (1) it only accepts real inputs, (2) it does not properly scale its output, and (3) it generates its output in the form of labels, which need to be extracted using the = IMREAL() and = IMAGINARY)) functions before they canbe plotted or otherwise used in subsequent calculations. Although it is possible to work around those limitations, it is far easier to avoid them by starting afresh, and to include frequency and time scales at the same time. This is what we have done here. 

---