Randomized Block Design ANOVA in Excel (with Easy Steps)

Sometimes you may need to do randomized block design ANOVA in Excel for any research purpose. In addition, you can find out lots of statistical terms like Summation, Count, Average, Variance, and the ANOVA summary table through one simple method. So, I will explain how to do randomized block design ANOVA in Excel in this article.

Download Practice Workbook

You can download the practice workbook from here:

What Is ANOVA?

Actually, this is a statistical test. Here, ANOVA (Analysis of Variance) summary table is important to understand whether the groups are significantly varying from each other. Basically, ANOVA finds the impact of different hypotheses in a treatment.

2 Steps to Do Randomized Block Design ANOVA in Excel

Here, I will describe detailed steps to do randomized block design ANOVA in Excel. Also, for your better understanding, I’m going to use a sample data that has 4 columns. Actually, I’m going to find out through this dataset if there are any differences among those detergents. Here, the higher marks denote more cleanness. The dataset is given below.

Now, you can apply the Data Analysis ToolPak to do randomized block design ANOVA in Excel. Firstly, you have to check whether your Excel Custom Ribbon is the Data Analysis ToolPak visible or not. If the Data Analysis ToolPak is invisible, then you should follow Step-1. Otherwise, you may follow from Step-2.

Here, I’m going to use Microsoft 365 to do randomized block design ANOVA in Excel.

Step-1: Inserting Data Analysis ToolPak for Randomized Block Design ANOVA

Here, I will show how to insert Data Analysis ToolPak in Excel.

• Firstly, you have to go to the File tab.

Eventually, you will see the following window.

• Now, from that window, you should choose the Options menu.

At this time, a dialog box named Excel Options will appear.

• Firstly, from that dialog box, you have to go to the Add-ins command.
• Secondly, choose Excel Add-ins in the Manage: box.
• Finally, press the Go button.

Additionally, another dialog box named Add-ins will appear.

• Now, you need to click on the Analysis ToolPak.
• Then, press OK to get the changes.

Lastly, you will see that there is a new ribbon named Data Analysis under the Data tab.

Read More: How to Make an ANOVA Table in Excel (3 Suitable Ways)

Step-2: Doing Randomized Block Design ANOVA in Excel

In this section, I will demonstrate how to use the Data Analysis ToolPak for randomized block design ANOVA in Excel.

• Firstly, from the Data tab >> select Data Analysis .

At this time, a dialog box named Data Analysis will appear.

• Now, select Anova: Two-Factor Without Replication and then Click on OK.

Subsequently, a new dialog box named Anova: Two-Factor Without Replication will appear.

• Firstly, select the data range in the Input Range: box for which you want to do the randomized block design ANOVA. Here, I have selected the range B5:E10.
• Secondly, mark the Labels option.

Here, Alpha is the significance value.

• Thirdly, choose the cell in the Output Range: box where you want to see the result. Here, I have chosen the G4 cell.
• Finally, press OK to get the result.

At this time, you will see the following output.

Basically, the Anova: Two-Factor Without Replication does some calculations like Count, Sum, Average, and Variance. Here, all the detergent types have those outputs, including each method. Firstly, the COUNT, SUM, and AVERAGE function works both horizontally (for Brands) and vertically (for Methods).

Additionally, among those brands which one is good, you can get from the average values. Remember, higher numbers denote higher cleanliness. And variance denotes the changing rate with the brand and methods.

And the following is the ANOVA summary table. Here, Rows denote blocks and Columns denote treatments.

Firstly, I will talk about the last three terms. Here, I will consider the null hypothesis when the P-value exceeds the significance value.  Otherwise, I will not consider the null hypothesis. Also, if the F value is less than the F critical value then there will be no difference between them. Which is the null hypothesis. So, there is no difference between those detergents or methods.

Now, let’s see the 1st three terms. Here, SS denotes the sum of squares, df denotes the degree of freedom and MS denotes mean squares.

Now, if I divide the sum square of rows by the total sum square then it returns 0.29. This means around 29% variance can be explained by the impact of detergent brands (blocks). When I divide the sum square of columns by the total sum square then it returns 0.022. This means around 2.2% variance can be explained by the impact of methods.

Read More: How to Use Two Factor ANOVA with Replication in Excel

Practice Section

Now, you can practice the explained method by yourself.

Conclusion

I hope you found this article helpful. Here, I have explained detailed steps to do randomized block design ANOVA in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Perform Regression in Excel and Interpretation of ANOVA
• How to Interpret ANOVA Single Factor Results in Excel
• Two Way ANOVA in Excel with Unequal Sample Size (2 Examples)
• How to Do One Way ANOVA in Excel (2 Suitable Examples)
• Do Two Way ANOVA in Excel (With Easy Steps)
• How to Graph Anova Results in Excel (3 Suitable Examples)
• Interpret Two-Way ANOVA Results in Excel