## 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.

**Read More:** How to Interpret ANOVA Results in Excel

**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.

**Read More:** How to Calculate P Value in Excel ANOVA

## 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**

**Related Articles**

- How to Make an ANOVA Table in Excel
- How to Perform Regression in Excel and Interpretation of ANOVA
- How to Graph ANOVA Results in Excel

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