Excel ANOVA

The MathCad worksheets used for this Chemometrics in Spectroscopy collaborative study series are given below in hard copy format. Unless otherwise noted, the worksheets have been written by the authors. The text files for the MathCad v7.0 Worksheets used for the statistical tests in this report are attached as Collabor GM, Collabor TV, ANOVA s4, ANOVA s2, CompareT, and Comp Meth. References [1-11] are excellent sources of information of the details on these statistical methods. 

The idea behind ANOVA is that if two distributions with very different means are combined, the variance of the resulting distribution will be much greater than the variances of the two distributions. Here I describe one- and two-way ANOVA in Excel, See (Massart et al 1997, chapter 6) for a more comprehensive discussion of the use of ANOVA,... 

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.6. (a) Excel Data Analysis ToolPak menu showing ANOVA options, (b) Single-factor ANOVA menu. 

Because of the limitations of Excel, I will not explore the use of the ANOVA further, but with appropriate software, or with a calculator and much patience, the method can be applied to any number of factors. 

Spreadsheet 2.8. Excel menu for two-factor ANOVA with replication. 

Results were analyzed by nested mixed-model ANOVA s using general linear procedures, in the MINITAB 15 statistical program. Nested mixed-model ANOVA was used when multiple leaves per tree and multiple trees per treatment were available. Additional analyses were linear and quadratic regressions (performed in MINITAB 15 and Excel), and when significant differences occurred, means were compared using Student s t-test or nested mixed-model ANOVA. 

Figure 2.2. ANOVA (single factor) with Excel. Output of ANOVA data analysis of NAD(H) assays of 15 samples from each of two tissues using Excel is shown. The difference in NAD(H) content of the two tissues are indicated by a small P value and F > Fom (reject H0).

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

The G-BASE project has used several statistical packages to perform this nested ANOVA analysis (e.g., Minitab and SAS). It currently uses an MS Excel procedure with a macro based on the equations described by Sinclair (1983) in which the ANOVA is performed on results converted to logio (Johnson, 2002). Ramsey et al. (1992) suggest that the combined analytical and sampling variance should not exceed 20% of the total variance with the analytical variance ideally being <4%. 

Johnson, C. C. (2002). Within site and between site nested analysis of variance (ANOVA) for Geochemical Surveys using MS EXCEL. British Geological Survey, UK, Internal Report No. 

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

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

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. 

The grading of reflection reports from the three student populations is analyzed and correlated with a number of other statistics and performance indicators. All data were collected in Microsoft Excel and unported into Statgraphics Centurion XV for further analysis [7], We performed ANOVA tests, multiple range test or Kruskal-Wallis tests, and f-tests on the gathered information. 

Since the P value of the ANOVA test does not exceed the chosen significance level a = 0.05, the regression is deemed significant. For this example, the FDIST function in Microsoft Excel [5, 6] was used to determine the P value, 3.41 x 10, of the ANOVA table (see Table 3.12). 

Can I do ANOVA with different numbers of replicates of an instance of a factor in Excel ... 

Why do I keep seeing an error message when I do a two-way ANOVA with replication in Excel ... 

In this chapter we briefly show how an ANOVA is performed for the simplest case of a single factor (so-called one-way ANOVA) and then for a two-way ANOVA. ANOVA is available (albeit in a restricted form) in Excel, and in most other statistical packages. Although we shall show you how to do a one-way ANOVA by hand, the chapter will concentrate on the interpretation of ANOVA output from software applications. 

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 only caters for one- and two-way ANOVA. In two-way ANOVA there are two factors being considered. For example, we may be interested in the effect of changing the catalyst and the temperature in a synthesis. One factor is the catalyst (e.g., Zn or Li) and the other is temperature (e.g., 50, 70, or 90°C). In two-way ANOVA in Excel the distinction is made between measurements that are repeated and those for which only a single measurement is made, at each combination of factors. The layout for this example is given in table 4.5. 

Determine whether temperature has a statistical effect on the decomposition of cinnamaldehyde using analysis of variance (ANOVA). (For how to perform ANOVA,. see S. R. Crouch and F. J. Holler. Applications of Microsoft Excel in Analytical Chemistry, Chap. 3, Belmont, CA Brooks/ Cole, 2004.) In the same wtty, determine if time of heating has an effect, (h) Using the data in part (g), assume that decomposition begins at 60°C and test the hypothesis that there is no effect of temperature or time. 

Note In a simple inter-laboratory study as given in this example, which does not further break down the inter-laboratory derived repeatability variance into its components, output from common spreadsheet software capable of one-way ANOVA such as Microsoft Excel, Corel Quattro, or free-to-download software such as OpenOffice.org Calc can also be used.) Table 9.21 and Equation (9.46) shows how output from a one-way ANOVA can be used. 

As for the ANOVA, the transformation also provides excellent results. The adjusted equation is now... 

Pareto ANOVA analysis is an excellent tool for determining the contribution of each input parameter and their interactions with the output parameters (surface roughness). It is a simplified ANOVA analysis method that does not require an ANOVA table. Further details on Pareto ANOVA can be found in Park [12]. 

The calculations for one-way ANOVA have been given in detail in order to make the principles behind the method clearer. In practice such calculations are normally made on a computer. Both Minitab and Excel have an option which performs oneway ANOVA and, as an example, the output given by Excel is shown below, using the original values. 

The one-way ANOVA output from Excel for this example is shown below. The results show that the between-sample mean square is greater than the within-sample mean square, and the result of the f-test shows that this difference is highly significant. 

---