Nested ANOVA in Excel (Detailed Analysis with Examples)

What Is ANOVA Analysis?

ANOVA is a statistical method used to analyze variance observed within a dataset. To perform an ANOVA analysis, we need to divide the dataset into two sections- systematic and random factors.

ANOVA lets us determine which factors significantly impact a given set of data.

There are two types of ANOVA, one is a single factor and another is two factors. In a single factor, ANOVA finds the effect of one factor on a single variable. On the other hand, there are multiple dependent variables in two factors ANOVA.

Overview of Nested ANOVA

A Nested ANOVA, as the name suggests, contains at least one factor nested inside another.

Let’s say there are two stores of a specific organization at two different places. For example, there are 2 teams- teams A and B in the first store and teams C and D in the second. Their sales assigned within their respective team would be nested within the store.

Figuratively, it will be as shown below.

The second factor is nested inside the first factor, so ANOVA analysis of such sales would be in the nested ANOVA category.

The data from the readings or data entries will look as shown below on the spreadsheet.

From this data, we can analyze two things- are the sales equal across each store (factor 1) and each team (factor 2)?

Upon performing ANOVA on this data (more of it in the later section), we will find something like this.

The p-values here will judge the significance of each factor. From this data, we can see that the store has a statistically significant effect whilst teams in them do not.

How to Perform Nested ANOVA in Excel

There is no direct way to perform a nested ANOVA in Excel. However we can still perform the operation by modifying the dataset and performing some manual calculations. We will utilize the Data Analysis Toolpak’s Two Factor with Replication feature here. You may not have the Data Analysis Toolpak by default in your ribbon. You will need to enable Data Analysis Toolpak. We will need the F.DIST.RT function for the manual calculation.

We need to modify the original raw dataset as we will be performing the two factor with replication on it. The sample dataset below will be used for illustration.

Steps:

Rearrange the dataset into the following. We included teams C and D’s sales values under A and B so that Excel can read them.

Go to the Data tab on your ribbon.
Select Data Analysis from the Analyze group section.

The Data Analysis box will appear.
Select Anova: Two-Factor with Replication under Analysis Tools.
Click OK.

Anova:Two-Factor With Replication box will open. Select the following particulars in the box.
Enter the range B4:D12 as the input range.
Insert 4 in the Rows per sample field, as we have four entries for each of the nested factors.
You can change the Alpha value too if you like. But we will keep it at 05 for now.
Select where you want your results to display under the Output options.

Click OK.
The result will pop up at the location you selected. We will use the last portion under ANOVA for the nested ANOVA analysis in Excel.

The p-value of the sample in cell F25 indicates the significance of the sample which is, in this case, the first factor, store.
For the significance of other factors, we now need to perform some manual calculations.
Select cell B34 and enter the following formula.

=B26+B27

Press Enter.

Enter the following formula in cell C34 and press Enter.

=C26+C27

Insert the following formula in cell D34 and press Enter.

=B34/C34

Enter the following formula in cell E34.

=D34/D28

Enter the following formula in cell F34 and press Enter.

=F.DIST.RT(E34,C34,C28)

Interpretation of the Result

If a p-value of a factor from an ANOVA analysis yields a value less than 0.05 it has a significant effect on the data. In this example, we had two factors- stores and teams, where teams were nested inside the store. The p-value of the store for this data is in cell F25 and the p-value of teams is in cell F34.

The p-value of the store is 0.011005 which is lower than 0.05. This indicates a significant effect on sales of this factor. Whilst p-value of the factor team is 0.9202 which is higher than 0.05. Which doesn’t indicate a significant effect on the data or sales. The store location is more important than the combination of employees for teams in this case.

Nested ANOVA in Excel: 2 Suitable Examples

Example 1 – Calculating Variance of Resistance with Different Lengths

We will use the following sample dataset.

There are two nests here – resistance values are nested inside length, which in turn is nested inside different samples.

Steps:

Go to the Data tab on your ribbon.
Select Data Analysis from the Analyze group section.

The Data Analysis box will open.
Select Anova: Two-Factor with Replication under Analysis Tools.
Click on OK.

Anova:Two-Factor With Replication box will open.
Enter the range B4:F12 as the input range.
Insert 4 in the Rows per sample field, as we have four entries for each of the nested factors.
You can change the Alpha value too if you like. But we will keep it at 05 for now.
Select where you want your results to display under the Output options.

Click on OK.
The result will pop up at the location you selected. We will use the last portion under ANOVA for the nested ANOVA analysis in Excel.

The p-value of the sample in cell F25 indicates the significance of the sample which is, in this case, the first factor, sample.
Select cell B34 and write down the following formula.

=B26+B27

Press Enter.

Enter the following formula in cell C34 and press Enter.

=C26+C27

Insert the following formula in cell D34 and press Enter.

=B34/C34

Enter the following formula in cell E34.

=D34/D28

Enter the following formula in cell F34 and press Enter.

=F.DIST.RT(E34,C34,C28)

Interpretation of the Result

The p-value in cell F25 indicates the significance of the sample (of different lengths) and the value of cell F34 indicates the significance of length on resistance. As both are below the alpha value of 0.05, both are significant factors in this example.

Example 2 – Analyzing Variance of Marks from Different Sections

We will use the following sample dataset.

This may look like a dataset for a two-way ANOVA, but this is a rearranged nested ANOVA for performing analysis in Excel.

Steps:

Go to the Data tab on your ribbon.
Select Data Analysis from the Analyze group section.

The Data Analysis box will open.
Select Anova: Two-Factor with Replication under Analysis Tools.
Click on OK.

Anova:Two-Factor With Replication box will appear.
Enter the range B5:F11 as the input range.
Insert 3 in the Rows per sample field, as we have three entries for each of the nested factors.
You can change the Alpha value too if you like. But we will keep it at 05 for now.
Select where you want your results to display under the Output options.

Click on OK.
The result will pop up at the location you selected. We will use the last portion under ANOVA for the nested ANOVA analysis in Excel.

The p-value of the sample in cell F25 indicates the significance of the sample which is, in this case, the first factor, section.
Select cell B34 and enter the following formula.

=B26+B27

Press Enter.

Enter the following formula in cell C34 and press Enter.

=C26+C27

Insert the following formula in cell D34 and press Enter.

=B34/C34

Enter the following formula in cell E34.

=D34/D28

Enter the following formula in cell F34 and press Enter.

=F.DIST.RT(E34,C34,C28)

Interpretation of the Result

The p-value in cell F25 indicates the significance of the section on the statistics. This value is greater than the alpha value of 0.05. The cell value of F34 is the p-value of subject marks in the dataset.

Things You Should Remember About Nested ANOVA

A nested ANOVA can have more than two factors. Like the one used in the first example, a factor can be nested into one factor. That factor can also be nested into another one on the hierarchy along with other factors of the same level.
A nested ANOVA is different from a two-way ANOVA. A two-factor ANOVA consists of two factors. A nested ANOVA must have one of those factors nested inside the other. Which isn’t the case for the two-way ANOVA.

Download Practice Workbook

Nested ANOVA.xlsx

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

Get FREE Advanced Excel Exercises with Solutions!