Analysis ToolPak

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

I Analysis ToolPak - VBA I Autosave Add-in I Conditional Sum Wizard r Dtools32... 

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

When you open the Tools menu, Data Analysis may not appear on the menu, in which case it needs to be installed. To do this, go back to the Tools menu and select Add-Ins... and tick the box for 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. 

TABLE 9-15. Regression Analysis Using Analysis ToolPak Add-In Fnnctionality from Excel... 

MULTIPLE LINEAR REGRESSION USING THE ANALYSIS TOOLPAK... 

Figure 11-14. Data obtained by using Regression from the Analysis ToolPak (from top) Regression Statistics, Analysis of Variance, Regression Coefficients and Statistics.

Figure 11-16. Degree of correlation of independent variables from the Analysis ToolPak.

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. 

Spreadsheet Summary In Chapter 3 of Applications of Microsoft Excel in Analytical Chemistry, the use of Excel to perform ANOVA procedures is described. There are several ways to do ANOVA with Excel. First, the equations from this section are entered manually into a worksheet, and Excel is invoked to do the calculations. Second, the Analysis ToolPak is used to carry out the entire ANOVA procedure automatically. The results of the five analysts from Example 7-9 are analyzed by both these methods. 

Spreadsheet Summary Chapter 4 of Applications of Microsoft Excel in Analytical Chemistry introduces another way to perform a least-squares analysis. The Analysis ToolPak Regression tool has the advantage of producing a complete ANOVA table for the results. A chart of the fit and the residuals can be produced directly from the Regression window. An unknown concentration is found with the calibration curve, and a statistical analysis is used to find the standard deviation of the concentration. 

Step 1 Under the Tools menu, click on Solver. Note If the choice Solver does not appear, choose Add-Ins and load Solver from the Analysis ToolPak or the original Excel program disk (or see your system administrator for help). 

In what follows we will assume that Windows and Excel have been installed in their complete, standard forms. For some applications we will also use the Solver and the Analysis Toolpak. These come with Excel, but (depending on the initial installation) may have to be loaded as an add-in. 

The functions listed in Tables 1.6-4 and 1.6-5 require that the Analysis Toolpak has been loaded. 

In Excel 97 and later versions you would click on Help, then select Contents and Index. This will give you three tabbed options first select Index, and type Analysis ToolPak (without the quotes). A number of choices appear, from which you can select general information . You are now offered three options Install and use Analysis ToolPak, Supplemental information about statistical methods and algorithms, and Ways to analyze statistics. Click on the first choice, and find out how to install the Analysis ToolPak. The same information would be available from Ways to analyze statistics. 

Say that you don t remember the name of the Analysis ToolPak, but instead look under data analysis . You will find a choice labeled Data analysis tools in the Analysis ToolPak, and from there the path to the information is the same. Or assume that you have selected the Contents instead of the Index. Browse the options (and use the vertical scroll bar to see more of them than can be displayed in the window) till you come across something that might fit. in this case, you will find the item Analyzing Statistical Data , which will again lead you to Install and use the Analysis ToolPak . In other words, with a little persistence you can find the required information almost no matter where you start there are many roads that lead to Rome. 

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. 

The weighted least-squares routine shown below provides the adjustable parameters and their standard deviations. If that is all you need, you may want to use it also as your general least-squares routine, especially after you have incorporated it in a menu or given it a toolbar icon (in which case it is easier to use and more readily accessible than the Regression routine in the Analysis ToolPak). When using it for unweighted least squares, merely leave the second column empty. Alternatively, if you desire the routine to provide more statistical information, you can modify it to do so. Remember, you are at the controls here. 

Most chapters start with a brief summary of the theory in order to put the spreadsheet exercises in perspective, and to define the nomenclature used. The standard versions of Excel 95 through Excel 2000 for Windows 95 or Windows 98 are used. Many exercises use the Solver and the Analysis ToolPak, both of which are available in the standard Excel packages but may have to be loaded separately, as add-ins, in case this was not done initially. When use of chapter 10 is contemplated, the VBA help file should also be loaded. 

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

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

---