hi everybody this is Eugene on our connection computing at the national college of ireland and welcome to my series of short how-to videos in this video I want to learn how to perform a two way ANOVA in Excel 2013 now before we start let's take a look at the data we're going to use here now I'm using some very small sample data here for illustration purposes only and these data here represent an experiment which is conducted with two brands of detergent one called super and one called best three temperature settings are used cold warm and hot so in this design here our factor temperature has three levels and our brand has two in a two way ANOVA we set out three null hypothesis our for Stage one is that the means of observations group by one factor are the same so in our example here this will be that there's no difference between the means of the super and best brands our second hypothesis H two is that the means of observations grouped by the other factor are the same in other words we're testing two here to see if there's a difference between the cold warm and hot temperatures and our third hypothesis is all about it the interaction between the factors H 3 is that there's no interaction between the two factors in other words the effect for example of temperature on dirt remove is not dependent on brands or want to test for that so just move the hypothesis over to the right hand side here now when I'm conducting a two-way ANOVA the ribbon I need first of all here is the data ribbon across the top of Excel and then over towards the right hand side I on the analysis pane I've got the data analysis toolpak option now if you don't see this here it means that you do not have this add-on installed in Excel you need to add this in separately so click on data analysis and this gives me our data analysis tool pack showing lots and lots of different statistics the one that we need to use here is the ANOVA two factor with replication because we have more than one observation for each combination of our variables here so select an over two factor word replication and click on OK now there just a few things you need to input here in the two factor word replication window first of all you notice that there's only one input range and so and that's very straightforward just select everything in your data so it's important that your data must be laid out like this you must have you can't have any null values in Excel you can't have any only even sample sizes and you also must have the same number of rows per sample as well so select everything including the labels so that's a 1 to d9 in my case here our second input box here is we must tell Excel how many rows per sample there are so if we look at our data we can see that there are four rows for super and four rows for best again these must be the same so I'm going to type in 4 here our third input value here is our alpha value that we want to use to test the default is 0.05 I'm going to leave it at that because that's a perfectly acceptable level of significance to conduct a test of this type and finally I want to display my output on the same sheet here so I'm going to select my output range check the output range box here in the middle and I'm going to select cell m f2 to put my output and display it and then click on OK now this gives me quite a lot of output here before I look at the ANOVA table down at the end let's quickly look at our other data Excel gives us a lot of summary data so for the Superbrand it's given me the count to sum the average in the variance for the Superbrand at each of the 3 temperatures it's also given me the descriptors for the best brand and it's also giving me the descriptors for the totals here as well so this is going to give you a useful information when you are interpreting your data after you've conducted the two-way and over now the important at table is the ANOVA table down here at the bottom of the screen it tells us our source of variation it tells us SS our sum of squares the number of degrees of freedom ms stands for mean square F is our F our Fischer statistic which we are looking to calculate our p-value here's our probability and finally the F crit is the critical value in the F distribution that would help us decide whether we accept or reject the hypothesis that we have on our screen here so first of all let's examine the ANOVA table and let's look at the first row here which is called the sample row in Excel and this row here tells us our F statistic and our p-value for differences between the super and best brand here if you get these confused sometimes the degrees of freedom here is a clue degrees of freedom are always the number of samples minus one so super and brand that's two minus one so we've got one degree of freedom here the first statistic we're going to look at is the F statistic we can see it's 9 point H 1 and that's greater than the critical value for this F statistic therefore we reject the null hypothesis that the means of the observations for the super and best factors are the same in favor of the alternative hypothesis that they are different we can also see that our p-value here is very small less than alpha 0.05 which is another evidence for rejecting the null hypothesis then we move to our columns which is is there a difference between the cold warm and hot values we can see a very high F statistic here which is way bigger than our critical value so forty eight point seven is our F our critical value is only three point five five therefore we once again reject our null hypothesis this time H two that the means of the observation by the columns called warm and hot here are the same in favor of the alternative is that there's a difference between at least two of them and we can also see here our p-value is tiny which is also evidence that we should reject H two finally we come to our interaction here and we can see that our F statistic is three point nine seven that this is greater than our F critical value of three point five five and this of course is set at alpha equal to 0.05 therefore with even though it's only marginally higher than our critical value we must reject the null hypothesis that there is no interaction between the two factors in favor of the alternate hypothesis that there is an interaction we can also see that our our p-value here M is less than our alpha value of 0.05 so we also will reject in that instance and what this tells us here indeed with the interaction is it tells us that the effect of temperature on the amount of dark removed is dependent on the brand it also tells us the reverse here which is that the effect of the brand on the amount of dirt removed is dependent on temperature so that's how you perform a two-way anova in Excel I hope you found this video useful thank you for your attention