How To Perform A One-Way ANOVA Test In Excel

in this video tutorial i'm going to show you how to perform a one-way analysis of variance or one-way anova by using microsoft excel this includes performing the test and analyzing the results as always i would really appreciate it if you would kindly drop a like on this video if you find it useful this really does help support and grow the channel and if you have any questions feel free to leave me a comment below so let's get into excel and get started for this tutorial i'll be using microsoft excel 365 pro plus i currently have some example data already entered so i'll briefly discuss these here i have data from three different mail groups for their performance on the vertical jump test each different group is in a different column then each cell represents a different participant's height that they jumped recorded in centimeters and as you can see i have 15 participants in each group so that's an overview of my data what i want to do now is to perform a one-way anova to determine if there is a significant difference between the average height measures of my three groups to be able to perform the one-way anova test easily in excel it's best to install or activate the analysis toolpak this is an add-on created by microsoft to provide data analysis tools for statistical analyses to install the tool pack go to file options then click on add-ins at the bottom you want to manage the excel add-ins and click the go button then ensure that you tick the analysis toolpak add-in and click ok now when you click on the data ribbon at the top you should see a data analysis button in a sub-section called analyze now we are ready to perform the one-way anova to do this click on the data analysis button then select anova single factor and click ok the first thing i need to do is select the input range this is essentially the data we want to run in the analysis so i will highlight all of my data you can highlight the labels in the first row if you want next i need to select how my data are grouped because each group's data is in a separate column this is grouped by columns so i'll select this option if your data was entered in rows instead of columns you could select the rows option if you highlighted the labels in your first row when selecting the input range then tick this option here since i highlighted my labels i will click this the next option is to specify your alpha level this is essentially your significance threshold usually this is set at 0.05 meaning that if the p-value was less than or equal to 0.05 you would reject the null hypothesis and accept the alternative hypothesis i will keep this set at 0.05 for this example finally we need to select our output options there are three options the output range lets you highlight a region within the sheet where you want the results to be entered the second option is to have the results placed on a new worksheet and you can give that sheet a name the last option is to have the results placed in a completely separate excel file i'll select the second option and call the new sheet results now i'll click ok to run the test so you should see a new sheet has been created in this case it is called results i'll now interpret the one-way anova results given by excel in the first table this presents a summary of the data in the analysis basically there is the count which is the number of data points in each group in this case there were 15 counts or participants in each group the sum is just the total value if we were to add up all of the values in each group next there is the average value in each group and the last column we have the variance the variance is the average of the squared differences from the mean it is simply a measurement of the spread between the numbers in the data set so that's a look at the summary table let me now focus on the anova results table underneath the results here are split into three rows the first row describes the results when the between groups is classed as the source of variation the second row describes the results when the source of variation is within groups and finally the last row is just the total simply it's just the sum of the ss and df for the first two rows but what does ss and the other columns represent the ss refers to the sum of squares the sum of squares between groups quantifies the variability between the groups of interest underneath is the sum of squares within groups and this quantifies the variability within the groups of interest next we have the df which stands for degrees of freedom this is calculated slightly differently for the two rows to determine the degrees of freedom between groups you simply subtract one from the number of groups in the analysis and since i have three groups one subtracted from three is two to determine the degrees of freedom within the groups you subtract the number of groups in the analysis from the total number of observations so in total i have 45 participants 15 in each group so to calculate the degrees of freedom within the groups i subtract 3 which is the number of groups from 45 next we have ms ms refers to the mean square you can think of it as an average variation between the groups or within the groups depending on the row you are looking at to calculate the mean square you simply divide the sum of squares by the degrees of freedom so if we take the between groups the mean square is 40.13 divided by 2 which is 20.07 if i round up the f statistic is the test statistic used in the one-way anova test it is calculated as the ratio of the mean square between the groups to the mean square within the groups the two degrees of freedom values along with the alpha level are used to work out the f critical value for the test this is easily calculated by excel or you can do this manually by looking up an f critical value table the f statistic is then compared with the f critical value if the f statistic is greater than the f critical value then we conclude that the test is significant and vice versa the p-value for the hypothesis test is also calculated my p-value here is 0.19557 by the way if you're not sure what a p-value is then i recommend that you watch my previous tutorial on what is a p-value in terms of hypotheses let's say my null hypothesis was there is no difference between the means of my three groups and let's say my alternative hypothesis was there is a difference between the means of my three groups so if you remember previously we selected the alpha level of 0.05 for our test this means that when p is less than or equal to 0.05 we will reject the null hypothesis and accept the alternative hypothesis or when p is greater than 0.05 we will accept the null hypothesis and reject the alternative hypothesis so since my p value is greater than my alpha level of 0.05 we fail to reject the null hypothesis therefore there is no difference between the means of my three groups in other words each group on average performed more or less the same on the vertical jump test now if your results were significant i.e the p-value was less than or equal to 0.05 then the one-way anova test can only tell you that there is a difference between the groups it's very important that you understand that the test will not tell you where these differences lie so you will not know which specific group is different from the rest to investigate this further you will need to perform a post hoc test there are many types of post hoc tests available such as tukey bonferroni and holmes's i'll discuss these in more detail and show you how to perform these in excel in future video tutorials and that brings me swiftly on to the end of this tutorial in this video you have learnt how to perform a one-way innova test in microsoft excel and you also know how to interpret the results if you found this video useful please leave a like it really does help support the channel if you've got a question pop it down in the comments below also consider subscribing for more weekly tutorials

Channel: Top Tip Bio

Views: 47,613

Rating: 4.9623656 out of 5

Keywords: one-way anova, Perform a one-way anova test in Excel, one way anova excel 2016, one way anova excel 2010, one way anova excel interpretation, how to interpret one way anova results in excel, anova test in excel, data analysis in excel, statistical analysis in excel, one way anova in excel data analysis, data analysis toolpak in excel, interpret one way anova results, microsoft excel tutorial, one-way analysis of variance excel, one way analysis of variance (anova) excel

Id: ZvfO7-J5u34

Length: 8min 31sec (511 seconds)

Published: Tue Feb 09 2021