Randomized Block Design ANOVA in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes you may need to do randomized block design ANOVA in Excel for any research purpose. In addition, you can find 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.


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.


How to Do Randomized Block Design ANOVA in Excel: 2 Steps

Here, I will describe detailed steps to do randomized block design ANOVA in Excel. Also, for your better understanding, I’m going to use 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.

Dataset for Randomized Block Design ANOVA in Excel

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.

Inserting Data Analysis ToolPak to do Randomized Block Design ANOVA in Excel

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.

Including Data Analysis Tool to do Randomized Block Design ANOVA


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 .

Use of Data Analysis Tool to do Randomized Block Design ANOVA in Excel

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.

Result of Randomized Block Design ANOVA in Excel

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.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section of Randomized Block Design ANOVA in Excel


Download Practice Workbook

You can download the practice workbook from here:


Conclusion

I hope you found this article helpful. Here, I have explained detailed steps to do randomized block design ANOVA in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo