Excel Data Analysis ToolPak

Spreadsheet 2.6. (a) Excel Data Analysis ToolPak menu showing ANOVA options, (b) Single-factor ANOVA menu. 

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 the first exercise in Chapter 3 of Applications of Microsoft Excel in Analytical Chemistry, we use Excel to perform the t test for comparing two means assuming equal variances of the two data sets. We first manually calculate the value of t and compare it with the critical value obtained from Excel s function TINV(). We obtain the probability from Excel s TDIST() function. Then, we use Excel s built-in function TTEST() for the same test. Finally, we employ Excel s Analysis ToolPak to automate the t test with equal variances. 

Spreadsheet Summary In Chapter 3 of Applications of Microsoft Excel in Analytical Chemistiy, we use Excel s Analysis ToolPak to perform the paired t text on the data of Example 7-7. We compare the results obtained with those found without pairing. 

There has been a realization that much of the basic data manipulation may be done in a spreadsheet, and for the present moment in the 21st century this means Microsoft Excel. The Data Analysis ToolPak... 

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. 

Similarly, in Excel 5 and 95, Help gets you to Microsoft Excel Help Topics and to the Answer Wizard. Click on either one, and you will see four tabs Contents, Index, Find, and Answer Wizard. When you select the Answer Wizard, type data analysis in the top window, then select an appropriate item from the list that appears in window 2. In this example, select Tell Me About Analyzing Statistics . Or you type analysis toolpak , and find How Do f Enable the Analysis ToolPak . Even How Do I Use the Analysis ToolPak will get you to the installation instructions. 

Excel has three built-in facilities for least-squares calculations, which provide the same (and, if you wish, much more) information. The first, LINEST, is a simple function. The second is the Regression macro in the Analysis Toolpak, which is part of Excel but must be loaded if this was not already done at the time the software was installed. The third (and often simplest) method is to use the Trendline feature, which is only available once the data appear in a graph. Later we will encounter yet another option, by using the weighted least squares macro described in chapter 10. Truly an embarrassment of riches Below we will illustrate how to use the first three of these tools. Table 2.6-1 lists their main attributes, so that you can make an informed choice of which one of them to use. 

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. 

Excel offers three flavors of ANOVA via its Analysis ToolPak ANOVA Single Factor ANOVA Two Factor with Replication and ANOVA Two Factor without Replication. The first is what we have just seen and accepts a data matrix set out as described, with variables in columns and repeats in rows. In this case the Grouped By Columns radio button is checked. Column headers in the first row can be included, which helps with interpreting the output. A value of a, the probability at which the null hypothesis will be rejected, must be specified for an / -test, with 0.05 being the default. Thus the F-value is tested at the 95% probability level. The output looks like that in table 4.3, except that the terms within groups and between groups are used, and there are two extra columns. One has the... 

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. 

On the Data ribbon in Analysis, look for Data Analysis. If Data Analysis does not appear, click the Office button (top left) and select Excel Options at the bottom of the window that opens. Click on Add-Ins at the left and select Analysis ToolPak. Click Go and then OK. Now Data Analysis should appear on your Data ribbon. 

---