ANOVA in Excel

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 

Example 3.5 looked at comparing two means for the glucose levels in soft drinks being analyzed by a spectroscopic enzyme assay and an alternative enzyme electrode method. The conclusion of the analyses, using a t-test, was that the two means were different. The analytical laboratory therefore decided to check each method relative to an AOAC (Association of Official Analytical Chemists) method that employed HPLC. The analytical results for six replicate measurements (units mM) using each method are  

Compare the means to determine whether there are significant differences between the methods at 95% probability. 

An efficient way of determining whether there is a significant difference is to do a one-factor ANOVA. The means and 95% confidence intervals are calculated (see chapter 2) and plotted in figure 4.2. 

First calculate the grand mean which is the mean of the all the data points. Therefore in Excel the overall mean is = AVERAGE(rangie) which for the above data included in spreadsheet 4.1 is = AVERAGE(A2 C7) giving the value 1.757. 

---

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

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. 

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

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

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. 

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. 

Two replications of MS-Nose evaluations with intensity scaling for each flavor solution were conducted in the same session, using the measuring sequence water, 4 flavor solutions (H-M-L-M) water, 4 flavor solutions (L-M-H-M) water. Linalool, cit-3-hexenol, and methyl cinnamate were measured on one day ethyl butyrate and amyl acetate were measured on another day a month later. Ion chromatograms were transformed to release curves in Excel and integrated. The parameter °log(Area) of the in vivo aroma release peak was used for all analyses in this chapter, although Imax and Tmax were also obtained from the release curves. The means and standard deviations for interaction terms (flavor component presentation order) from an unbalanced analysis of variance (ANOVA) were determined for both the MS-Nose data and the panelists intensity scores. 

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. 

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

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

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. 

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. 

Table 7.4 shows the result of the Minitab calculation for these results. (NB. In using this program for two-way ANOVA calculations with interaction, it is essential to avoid the option for an additive model the latter excludes the desired interaction effect. Excel also provides facilities for including interaction... 

---