ANOVA Excel Worksheet

Question

The following table contains a random sample of 40 women partitioned into three groups:
Group 1: ages below 20
Group 2: ages 20 through 40
Group 3: ages over 40
the values in the table are systolic blood pressure levels
The Hypothesis test:
H₀: μ₁=μ₂=μ₃
H₁: at least one of the treatment means is different
use the Excel Analysis Toolpak to create an Anova- Single factor table.
is there sufficient evidence to support the claim that women in different age categories have different mean blood pressure levels? Give for your decision.


Group 1    Group 2    Group 3    [Place your Anova table here]
   104           97            123
   106           116          107       Anova: Single Factor
   104           98            127
   92             95            133      SUMMARY
   112           108          114      Groups    Count    Sum    Average    Variance
   107           106          93        Group 1
                   102          113      Group 2
                   108          114      Group 3
                   94            116
                   100          155
                   101          105       ANOVA
                   119                     Source of Variation    SS    df    MS   F    P-value    F-crit
                   89                       Between Groups
                   113                     Within Groups
                   99
                   93                       Total
                   107
                   125
                   104
                   110
                   124
                   181
                   118

Answer 1

late to the party! But I think this is what the asker meant for people coming back later.

Anova: Single Factor

SUMMARY
Groups Count Sum Average Variance
group 1 6 625 104.1666667 44.17
group 2 23 2507 109 344.91
group 3 11 1300 118.1818182 267.56


ANOVA
Source of Variation SS df MS F P-value F crit
Between Groups 937.93 2 468.97 1.655 0.205 3.252
Within Groups 10484.47 37 283.36

Total 11422.4 39

 And the P-Value of .205 is greater .05 so I believe we can say with 95% confidence that the populations are different. But I'm not a stats guy so take that with a grain of salt.

Answer 2

As found on Microsoft Excel Help:

If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.

The Analysis ToolPak includes the tools described below. To access these tools, click Data Analysis in the Analysis group on the Data tab. If the Data Analysis command is not available, you need to load the Analysis ToolPak add-in program.

 

Click the File tab, click Options, and then click the Add-Ins category.
In the Manage box, select Excel Add-ins and then click Go.
In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.

Tip:  If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.

If you are prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
 

 

Note    To include Visual Basic for Application (VBA) functions for the Analysis ToolPak, you can load the Analysis ToolPak - VBA Add-in the same way that you load the Analysis ToolPak. In the Add-ins available box, select the Analysis ToolPak - VBA check box.

 
Anova
The Anova analysis tools provide different types of variance analysis. The tool that you should use depends on the number of factors and the number of samples that you have from the populations that you want to test.

Answer 3

Here are the required steps to run ANOVA Single Factor using Data Analysis Tools:

1) First copy the three groups data including labels into columns A through C.

2) Gog to Data tab>Click on Data Analysis> choose ANOVA: Single Factor

3) Choose the Input range which would be $A$1:$C$24, Grouped By : Columns, Check box Labels in First Row, Alpha: 0.05, and then click on OK.

Here is the results:

Anova: Single Factor
SUMMARY
Groups	Count	Sum	Average	Variance
Group 1	6	531	89	1523
Group 2	23	2507	109	345
Group 3	11	1300	118	268
ANOVA
Source of Variation	SS	df	MS	F	P-value	F crit
Between Groups	3437	2	1718	4	0.039	3
Within Groups	17879	37	483
Total	21316	39

As P-value (0.039)<0.05 we strongly reject the null hypothesis. Thus, we support the claim that women in different age categories have different mean blood pressure levels (alternative hypothesis)