 |
Statistics Tutorials
for
Statistical
Data Analysis:
SAS, SPSS, WINKS, Excel
For more professional statistical results:
WINKS Statistical Software
Affordable. Reliable. Relevant.
Analyze with WINKS.
www.texasoft.com
ANOVA Using Microsoft Excel
One-Way Analysis of VarianceThese tutorials briefly explain the use and interpretation of standard statistical analysis techniques. The examples include how-to instructions for Excel. Although there are different version of Excel in use, these should work about the same for most recent versions. They also assume that you have installed the Excel Analysis Pak which is free and comes with Excel (Go to Tools, Addins... if it is not already installed in your version of Excel.)
See www.stattutorials.com/EXCELDATA for files mentioned in this tutorial, © TexaSoft, 2008
Performing an ANOVA in Excel
Definition: An Independent Group ANOVA is an extension of the independent group t-test where you have more than two groups. This test is used to compare the means of more than two independent groups and is also called a One Way Analysis of Variance.
Assumptions: Subjects are randomly assigned to one of n groups. The distribution of the means by group are normal with equal variances. Sample sizes between groups do not have to be equal, but large differences in sample sizes by group may effect the outcome of the multiple comparisons tests.
Test: The hypotheses for the comparison of independent groups are: (k is the number of groups)
Ho: u1 = u2 ... = uk (means of the all groups are equal)
Ha: ui <> uj (means of the two or more groups are not equal)
The test is performed in an Analysis of Variance (ANOVA) table. The test statistic is an F test with k-1 and N-k degrees of freedom, where N is the total number of subjects. A low p-value for this test indicates evidence to reject the null hypothesis in favor of the alternative. In other words, there is evidence that at least one pair of means are not equal.
Example: Independent Group ANOVA (One-Way Analysis of variance)
The FEED_ANOVA.XLS file contains information on four different feeds and weight gain of animals after they had been fed one of the feeds for a period of time. You want to know if any feed is better for producing weight gain.
A B C D 60.8 78.7 92.6 86.9 67 77.7 84.1 82.2 54.6 76.3 90.5 83.7 61.7 79.8 90.3 Step 1: Open the file FEED_ANOVA or enter thedata into an Excel datasheet.
Step 2: In Excel 2003 or earlier, pull down “Tools” to “Data Analysis” In Excel 2007 click on Data then Data Analysis.
Step 3: Select Anova: Single Factor.
Step 4: In the following Dialog box, enter the input range that corresponds to the data columns ($A$1:$D$5) and click OK. Check the option "Labels in First Row".
The tesults appear in a new worksheet, as shown here:
In this output, the test statistic, F, is reported in the analysis of variance table, F(3,11) = 39.82 . The p-value for this statistics is p< 0.001 (reported in the table as 3.36E-E06). This means that there is evidence that there are differences in the means across groups.
Unfortunately, Excel does not include a standard multiple comparison test you can use to determine which means are different from the others.
Step 5: One way to determine specific difference is to perform paired analyses of the group, two at a time. For example, compare the mean for group A vs the mean for group B, then A vs C then A vs D and so on. In Excel, your option is to do this using multiple two-sample t-tests.
If you do these pairwise comparisons, you should modify the resulting p-value for each t-test, since performing multiple t-tests increases the probablity of finding an incorrect significance. To correct for this problem you should multiple the p-values for each of the pair-wise comparisons by the number of comparisons. This is called a Bonferonni adjustment. For example, in this case your comparisons are
A vs B, A vs C, A vs D, B vs C, B vs D, and C vs D -- 6 pairwise comparisons in all. Thus, you'd correct each t-test p-value by multiplying it by 6.
For example, a t-test comparison of Mean A vs Mean C (61.025 vs 89.067) yields an unadjusted two-tail p-value p=0.0006. The adjusted p-value (the one you should report) would be 0.0006*6 = 0.0036
End of tutorial
See http://www.stattutorials.com/EXCEL
For Excel data files click here
For more information... we recommend:
- WINKS Software-- a simple, inexpensive alternative to Excel -- easy to use, reads Excel file, and provides much more explanation for results -- to use and affordable statistical software program that will help you analyze, interpret and write-up your results. Download a free trial copy. WINKS reads Excel files, so you can read your Excel data into WINKS and do many more types of analysis than in Excel alone.
- SAS Essentials: Mastering SAS for Research
- provides an introduction to SAS statistical software, the premiere statistical data analysis tool for scientific research. Through its straightforward approach, the text presents SAS with step-by-step examples. SAS Essentials introduces a step-by-step approach to mastering SAS software for statistical data analysis. It's also a valuable reference tool for any researcher currently using SAS. Designed for those new to SAS and filled with illustrative examples, the book shows how to read, write and import data; prepare data for analysis; use SAS procedures; evaluate quantitative data; analyze counts and crosstabulation tables; and compare means using the t-test. The book also provides instruction and examples on analysis of variance, correlation and regression, nonparametric analysis, logistic regression, creating graphs, controlling outputs using ODS, as well as advanced topics in SAS programming.ISBN: 0470461292. Order from publisher -Jossey-Bass/Wiley. Barnes & Noble. Amazon.
- Against All Odds VIDEOS - Now in DVD format -- Teaching Videos from Annenberg/PBS --"This highly engaging primer on statistical methods and inference introduces the practical applications of statistics. Produced by the Consortium for Mathematics and Its Applications and Chedd-Angier." Click here for info
- BeSmartNotes Reference sheets for SAS, SAS ODS, SPSS and WINKS - Click here for info.
- Statistical Analysis Quick Reference Guidebook: With SPSS Examples is a practical "cut to the chase" handbook that quickly explains the when, where, and how of statistical data analysis as it is used for real-world decision-making in a wide variety of disciplines. It contains examples using SPSS Statistics software. In this one-stop reference, the authors provide succinct guidelines for performing an analysis, avoiding pitfalls, interpreting results, and reporting outcomes. Paperback. Sage Publishers ISBN: 1412925606 Order book from Amazon
| Send comments | Back to Tutorial Menu | TexaSoft |