Osman Goni Ridwan
In the era of Data Analysis, you may need to do variance analysis in Excel. You can use One Way ANOVA or Two Way ANOVA to do variance analysis in Excel. I am showing the procedure in this article with clear steps and illustrations and explanations of the outcomes of ANOVA so you can easily do variance analysis in Excel and make a variance report.
Download Practice Workbook
You can download the practice workbook from here:
What Is ANOVA (Analysis of Variance) in Statistics?
Analysis of Variance (ANOVA) is a statistical method that divides the observed possible factors within a dataset into two sections:
- Systematic Factors: They have a statistical impact on the supplied data set.
- Random factors: They have no statistical impact on the supplied data set.
Data Analysts employ the ANOVA test to examine the impact of independent factors on the dependent variable. So, they can decide which factors to take for further analysis and which to reject.
The Formula for ANOVA Is:
where:
F=ANOVA coefficient
MST=Mean sum of squares due to treatment
MSE=Mean sum of squares due to error
The Outcome of Analysis of Variance ANOVA
ANOVA is the first stage in determining which factors influence a particular data set. After the test is completed, an analyst does extra testing on the methodological factors that measurably impact the inconsistency of the data set. And he uses the ANOVA test findings in an f-test to create extra data that is relevant to the proposed regression analysis. The ANOVA test compares more than two data sets at the same time to see whether there is a link between them.
One-Way ANOVA vs. Two-Way ANOVA
ANOVA is of two types: One-way or unidirectional and Two-way. The way relates to the number of independent variables in a variance analysis test.
- In Two-Way ANOVA, there are multiple dependent variables and in One-Way, there will be one dependent variable.
- One-Way ANOVA calculates the effect of one single factor on a single variable. And it checks whether all the sample data sets are of same or not.
- One-way ANOVA determines the significant differences between the average means of multiple independent variables.
- Two-way ANOVA determines the effect of multiple independent factors on a single dependent factor. So, you can decide which factors you can choose for further analysis.
For Example:
- An educational institution evaluates the student performance compared with the quality of teachers then it will use One-way ANOVA.
- If you calculate satisfaction levels among certain types of professions and want to see the variance then you will use One-way ANOVA.
- If you want to know the effect of factors on the satisfaction level of a profession, you will use Two-Way ANOVA. Here satisfaction level is the dependent variable and the factors like work environment, work pressure, employment type, etc will be the independent factors here.
Steps to Do Variance Analysis in Excel with One Way ANOVA
To do variance analysis, you should have a data set first. Here, suppose you have a dataset that contains data on the satisfaction level of people in various professions. Here is a list is shown of 9 people from each profession.
Step 1: Go to Data Analysis Option
- At first, go to the Data tab in the top ribbon,
- Select the Data Analysis tool
Step 2: Select ‘Anova: Single Factor’
- Now, the Data Analysis window will appear.
- Here, select the Anova: Single Factor option.
- Then press Ok.
Final Step: Set Input Range and Press OK
- Now, the Anova: Single Factor window will open.
- Here, select the cells in the Input Range box.
- Mark the box named “Labels in First Row”
- And press OK.
- As a result, a new worksheet will create and the ANOVA outcomes will be here.
One-Way ANOVA Results Explanation:
- In the Summary table, you will find the average and the variances of each profession. Here you can see the average satisfaction level is 6.11 for Doctors but the variance is 12.11 which is very high than others. That means, in the Doctors profession, some are highly satisfied and some are very less.
- Here, ANOVA results are not that significant as you are calculating only the variances.
- Here, P-values interpret the relation between the columns and the values are greater than 0.05 so this is not statistically significant. And there shouldn’t relation between the columns also.
Read More: How to Calculate Variance in Excel (Easy Guide)
Similar Readings
- How to Calculate Coefficient of Variance in Excel (3 Methods)
- Calculate Mean Variance and Standard Deviation in Excel
- How to Calculate Sample Variance in Excel (2 Effective Approaches)
- Calculate Variance Percentage in Excel (3 Easy Methods)
Steps to Do Two-Way Variance Analysis in Excel
Suppose you have some data on student attendance in a school. There are two shifts in that school. You have found some clue that there is some effect of the day temperature on the presence of students but it also varies in shifts. You want to Data Analysis of the ready data to find a relation between the attendance and shift and temperature. I am showing the steps below.
Step 1: Go to Data Analysis Tool
- First, go to the Data tab.
- Then, select the Data Analysis option.
Step 2: Select Anova (Two-Factor with/without Replication)
- Now, in the Data Analysis window, select the “Anova: Two-Factor With Replication” option.
- And press OK.
Last Step: Set Input Range to Get Result
- Now, a new window will appear.
- Here, select the data range in the Input Range box.
- Then input 4 in the Rows per sample box as you have 4 rows per shift.
- Finally, press OK.
- As a result, you will see a new worksheet created.
- And, two way ANOVA result is shown in this worksheet.
Two Way ANOVA Results Explanation:
Here, the first tables, there are showing the summary of shifts. In brief:
- The average presence in Morning shift while temp < 30C is 65.5 but in the Day shift is 83.75
- But when the temp is >36C, the average presence in the Morning shift is 82% but in the Day shift is 70%.
- Variance is very high at 91 in the Morning shift when temp <30C.
- You will get a complete overview of data in the summary
And in the ANOVA part, you can summarise the interactions and the individual effects. In brief:
- The P-value of shifts is 0.6 which is not statistically significant at the alpha level of 0.05.
- The P-value of Temperatures is 0.036 which is statistically significant so you can say that there is an effect of temperatures on the presence in the school. But the value is close to the alpha value of 0.05 so the effect is less significant.
- But the P-value of interactions is 6.29 e-05 which is much less than the alpha value so it is very much significant statistically and you can say that the combined effect of shift and temp is very high in the presence in the school.
Read More: How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
What to Do When Data Analysis Tool Is Not Showing?
The Data Analysis option will not show if the Data Analysis Toolpak Add-in is not enabled. Here I will show to enable the Data Analysis Toolpak in Excel.
- First, go to the Options from the File tab.
- Then, go to the Add-ins option.
- Here, select Excel Add-ins in the Manage drop-down menu.
- And press Go.
- Then, a new window will appear.
- Here, mark the option “Analysis ToolPak” And press Ok.
- Now, go to the Analyze menu in the Data tab. Here you will find the Data Analysis option.
Conclusion
In this article, you have found how to do variance analysis in Excel. I have shown both one-way ANOVA and two-way ANOVA to do the variance analysis in Excel. You can download the workbook for free and practice them yourself. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
