Analysis of Variance (ANOVA) in MS Excel 2010

1
Analysis Of Varianceanova inMicrosoft Excel 2010
2
Analysis of variance (ANOVA)

• Anova Single Factor --- InsectSprays dataset
• 2 way ANOVA without replication (aka Randomized
  block design) --- Scrips2block dataset
• 2 way ANOVA with replication --- Salary2way
  dataset

3
Anova Single Factor

• Factor variable is brand of insect spray
  response variable is the insects count.
• This experiment is to analyze the effectiveness
  of six insect spray brands A,B,C,D,E and F
• The columnar data shows the count of insects for
  each of the six brands.

4
Anova Single Factor

• Null Hypothesis Insect count across brands
  A,B,C,D,E, and F are equal
• Alternate Hypothesis Insect count across brands
  A,B,C,D,E, and F are not equal

5
Analysis ToolPak --- Add In
6
Excel Data Analysis Pak --- Add In
7
Excel Data Analysis ToolPak --- Add In
8
Excel Data Analysis ToolPak --- Add In
9
Excel Data Analysis ToolPak --- Add In
10
Excel Data Analysis ToolPak --- Add In
11
Excel Data Analysis ToolPak --- Add In
12
Analysis of varianceSingle Factor
13
Anova Single Factor
14
Anova Single Factor
15
Anova Single Factor - Input cells
16
Anova Single Factor Output Area

• Select the input data range.
• Tick Labels in first Row.
• Let Aplha 0.05 (which is the default value).
• Select the output option and indicate the cell
  location.

17
Anova Single Factor Output Area
18
Anova Single Factor --- Results
19
Anova Single Factor --- Results

• The response variable which is the number counts
  of insect against the factor insect spray brand
  is significant, as indicated by the p-value which
  is less than 0.05

20
Anova Two Factor without Replication
21
Anova Two Factor without Replication

• Dataset --- Scrips2block
• There are six investors and each the investors
  invest with all the three brokers.
• The brokers are assigned in a randomized manner
  to the six investors and hence this method is aka
  Randomized Block Design.
• Response variable is the returns achieved by the
  investors (aka blocks).

22
Anova Two Factor without Replication
23
Anova Two Factor without Replication
24
Anova Two Factor without Replication

• Select the input data range.
• Tick Labels in first Row.
• Let Aplha 0.05 (which is the default value).
• Select the output option and indicate the cell
  location.

25
Anova Two Factor without Replication
26
Anova Two Factor without Replication
27
Anova Two Factor without Replication

• Sum of squares against Row indicates that the
  variation due to block factor variable
  investors is significant .
• Sum of squares indicated against Column refers
  to the effect due to the factor variable Broker
  (A,B, and C).
• Both the effects are significant.

28
Analysis of variance Two Factor with Replication
29
Anova Two Factor with Replication
30
Anova Two Factor with Replication
31
Anova Two Factor with Replication
32
Anova Two Factor with Replication

• Source of Variation
• Sample --- indicates the variation due to gender
• Column --- indicates the variation due to
  designation
• Interaction --- is the variation due to
  interaction between the two factors
• The variation due to the two factors and the
  variation due to interaction between the factors
  are significant, as shown by the results.

33
Thank You