Two Way ANOVA in Excel with Unequal Sample Size (2 Examples)

The full form of the ANOVA is Analysis of Variance. This test helps us to identify whether there is a similarity in values between two data samples. or whether one dataset is significant compared to the other. The two-wayANOVA analysis means there should be involvement of two separate variables, and how they interact with each other. If you are curious to know how you can analyze two-way ANOVA in Excel with unequal sample sizes, then this article may come in handy for you. In this article, we will discuss how you can analyze two-way ANOVA in Excel with an unequal sample size with elaborate explanations.

Overview of ANOVA Analysis

ANOVA provides the first opportunity to determine which factors have a significant effect on a given set of data. After the analysis is completed, an analyst does extra analysis on the methodological factors that significantly impact the inconsistent nature of data sets. And he uses the ANOVA analysis findings in the F-test in order to create extra data relevant to the estimated regression analysis. The ANOVA analysis compares many data sets simultaneously to see whether there is a link between them. ANOVA is a statistical method used to analyze variance observed within a dataset by dividing it into two sections: 1) Systematic factors and 2) Random factors

The Formula of ANOVA:

F= MSE / MST

Here:

F = ANOVA coefficient

MST = Mean sum of squares due to treatment

MSE = Mean sum of squares due to error

ANOVA is of two types: single factor and two factors. The method is related to variance analysis.

In two factors, there are multiple dependent variables and in one factor, there will be one dependent variable.
Single-factor 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.
Single-factor ANOVA identifies the differences that are statistically significant between the average means of numerous variables.

Two Way ANOVA with Unequal Sample Size in Excel: 2 Suitable Examples

We are going to do variance analysis by following the method of two factors without replication of ANOVA Analysis. Furthermore, We have some data on different exam scores of a school. Additionally, we have some data on the work hours of men and women. Both of them actually have unequal sample sizes. You want to do a Data Analysis of the ready data to find a relation between two groups in each example. Let’s walk through the steps to do a two-way ANOVA analysis without replication with unequal sample sizes.

Example 1: Two Way ANOVA for Student Marks

In the following example, we are going to use the dataset where we have the student’s marks in two separate groups. One group is Early Riser and another one is Late Riser. And the first group is one row short compared to the second group. We will apply ANOVA analysis to these two groups to see how they interact.

Steps

In the beginning, we need to first add Data Analysis ToolPak. Adding this toolpak will allow us to ANOVA analysis in two ways.
To add the Data Analysis Toolpak, click on the File in the corner of the worksheet.

Then from the Excel Options dialog box, click on the Add-ins.
Right after this, from the Manage option, choose Excel Add-ins.
Click Go after this.

In the next Add-ins dialog box, tick the Analysis ToolPak checkbox.

Now you can see that the Data Analysis command is now present in the Data tab.
Click on the Data Analysis command.

In the Data Analysis dialog box, select ANOVA: Two-Factor With Replication and then click on OK.

After clicking OK, you can see that there is a new dialog box, named ANOVA: Two-Factor Without Replication. In that dialog box, you need to select the input data for ANOVA calculations.
Select the range of cell B4:E12 in the Input Range.
Tick the Labels check mark.
Now select the proper alpha value for the analysis. We are going for the usual 0.05 here.
And select cell $G$2, for the selection for the Output Range.
Click OK after this.

After clicking on OK, the ANOVA two factor with replication result will appear on a new or already existing Excel spreadsheet depending on the option you have selected.

Example 2: Two Way ANOVA for Working Hours

In the following example, we are going to use the dataset where we have the working hour in two separate groups. One group is for the Man and another one is for Women. And the first group is one row short compared to the second group. We will apply ANOVA analysis to these two groups to see how they interact.

Steps

You can see that the Data Analysis command is present in the Data tab.
Click on the Data Analysis command.

In the Data Analysis dialog box, select ANOVA: Two-Factor Without Replication and then click on OK.

After clicking OK, you can see that there is a new dialog box, named ANOVA: Two-Factor Without Replication. In that dialog box, you need to select the input data for ANOVA calculations.
Select the range of cells B4:E12 in the Input Range.
Tick the Labels check mark.
Now select the proper alpha value for the analysis. We are going for the usual 0.05 here.
And select cell $G$2, for the selection for the Output Range.
Click OK after this.

After clicking on OK, the ANOVA two-factor without replication result will appear on a new or already existing Excel spreadsheet depending on the option you have selected.

Interpretation of the Result

The interpretation of any ANOVA analysis involves the Null hypothesis test. The Null hypothesis is that no comparing group is significantly different from the other group. If F > F_criticalthen we can reject the Null hypothesis. But on the other hand, if F < F_critical, then we can’t reject the Null Hypothesis, meaning that no comparing group is significantly different from any other group.

In the first example, we can see that in both cases, F < F_critical. This means we can’t reject the null hypothesis in those cases. Subsequently, this also means that there are strong similarities between the datasets.
In the second example, we can see that in row cases, F < F_critical. This means we can’t reject the null hypothesis in those cases. Subsequently, this also means that there are strong similarities between the row of the datasets. But in column cases F > F_critical, which means that we can reject the Null hypothesis there. Subsequently, this also means that there aren’t any strong similarities between the datasets in column sections.
We can also see that the P-value in the first example is 0.037 which is statistically significant so you can say that there is an effect of shifts on the performance of the students in the exam. But the value is close to the alpha value of 0.05 so the effect is less significant.
The P value in the first example is 0.510. So you can say that the working hour of the students have some significant impact on the marks they get. But as this value is not very close to the value of alpha, the effect is substantial. Meaning this has a heavy impact on the performances.
The P value in the first example is 0.0346. So you can say that there is an impact of gender in the distribution of workhour. But as this value is very close to the value of alpha, the effect is non-substantial. This means this gender impact is not very effective.

Limitations of Two Way ANOVA Analysis with Unequal Sample Size in Excel

In this example, we showed how you could execute the ANOVA analysis with an unequal sample size. But there is a couple of restriction on calculating the ANOVA analysis with these specific criteria.

There are no criteria like having the same equal sample sizes for ANOVA calculation. So theoretically this ANOVA analysis with unequal sample sizes can be done for both one-way and two-way analyses.
But there is a small rule. The variance difference between the sample datasets must be close. If the unevenness of sample sizes between the dataset is too big, then ANOVA analysis can create a problem. But for small variance differences, ANOVA can be very robust.
For the factorial ANOVA, use can face heavy issues.
Now for the Excel. Things are a lot more complicated than it seems.
For homogeneous types of data and equal cell size, Excel can execute the ANOVA analysis quite easily. But if the data samples lack homogeneousness and have different cell sizes, then excel refuses to execute the ANOVA analysis.
It only can execute the ANOVA analysis in two ways without replication provided that the rows are different numbers only.
In two ways with replication, both the row and the column needs to be the same in the dataset.
This is because Excel is not efficient software for carrying out calculations with varied datasets. Variations of datasets make Excel throttle. Other software like the SPSS/R could easily deal with this type of situation.

Things You Should Keep in Mind

Excel’s ANOVA tool works perfectly whether we input the correct data or risk incorrect data.
To acquire an exact F Value, ensure the initial variance is less than the second variance.

Download Practice Workbook

Download this practice workbook below.

Two Way ANOVA with Unequal Sample Size.xlsx

Conclusion

To sum it up, the issue of how you can do a two-way ANOVA analysis in Excel with an unequal sample size in Excel is answered here with 2 different examples with elaborate explanations.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section.

<< Go Back to Anova in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!