How to Perform Statistical Computations Using Microsoft Excel: Analysis of Variance for Complete Random Design
Auto Beauty Business Culture Dieting DIY Events Fashion Finance Food Freelancing Gardening Health Hobbies Home Internet Jobs Law Local Media Men's Health Mobile Nutrition Parenting Pets Pregnancy Products Psychology Real Estate Relationships Science Seniors Sports Technology Travel Wellness Women's Health

How to Perform Statistical Computations Using Microsoft Excel: Analysis of Variance for Complete Random Design

A step-by-step procedure on how to solve ANOVA for Complete Random Design using Microsoft Excel...

Statistical computation is one of the hardest parts in the analysis of numerical data. Though there are specialized software programs designed to perform statistical computation and analysis, this operation can be done in Microsoft Excel, a program very common and familiar to us all.

The default Microsoft Excel does not immediately present the tools necessary for deeper statistical computation. However, these tools or add-ons are actually present in Excel but are not yet installed. To access and use them, you have to install them first. In my previous article (Statistical Computing Using Microsoft Excel: Basic Statistics), I have detailed the procedure on how to install the add-ons on Microsoft Excel. Please see link to follow the installation procedure.

After that, Data Analysis command would be added to the Data menu. You can now use this to perform statistical computation.

One of the hardest applications of statistics is in the ANOVA or Analysis of Variance. ANOVA is a method of analyzing data by testing if the means of the groups formed by values of the independent variables are different enough not to have occurred by chance. In simpler words, we would test if the means of our samples are significantly different from each other or that the differences of their means are not due to chance or error.

ANOVA can be applied to different experimental designs. For this discussion, let us consider CRD or Complete Random Design. In this layout, experimental units or samples are essentially homogeneous and are arranged in complete randomness.

As an example, let us consider an experiment with one independent or explanatory variable. The table below shows the compressive strength (psi) of bricks made from varying mixture of coconut husk (CH) and clay.

Compressive Strength (psi) of Bricks

 

Rep 1

Rep 2

Rep 3

Mean

Treatment 1: 75% CH + 25% Clay

1350

1320

1347

1339

Treatment 2: 60% CH + 40% Clay

1380

1405

1394

1393

Treatment 3: 50% CH + 50% Clay

1485

1460

1450

1465

Treatment 4: Commercial Bricks

1500

1550

1480

1510

To solve for the ANOVA:

1. Encode the data on Microsoft Excel.

2. Go to the menu and look for Data. Click it and then Data Analysis.

3. A dialog box opens. Find Anova: Single Factor using the scroll bar and double click it.

4. Another box opens in which you will have to fill in the necessary information in order to perform the computation.

5. The first entry is the Input Range. There is a text box in which you must supply the cells that contain the data to be analyzed. The button beside it, the one with the red arrow, allows you to easily select the cells. Click it and highlight the cells. Take note that it is more preferable to choose the cells with the data only.

Click the button again to return to the dialog box.

6. The next entry (Grouped by: ) asks you if how  the data are grouped. Are the treatments found in rows or columns? For our example, select Rows.

7. There is a check box for Labels in first column. Check it if you have also highlighted the cells containing the name of the treatments. In the case of our example, we only choose cells with numerical data. Hence, leave the box unchecked.

8. Next is Alpha. In the box, type the level of significance you are considering. For our example, let’s say that the significance level is 1% or 0.01. Type 0.01 in the box.

9. Lastly, the Output options help you choose where to display the results. If you choose Output Range, results will be shown on the cell you selected on the current page. If you choose New Worksheet Ply, a new page opens with only the results displayed. If you opt for New Workbook, a new workbook or document opens with the results. For our example, and for convenience sake, let us choose output range.

 

Click the button with the red arrow and select a cell where your results will be shown. After selecting a cell, click the same button again.

10. You are now done supplying the needed information. The very last thing to do is click the OK button.

11. Voila! The ANOVA table is now presented right before your eyes.

Based on the results generated, the p-value is less than alpha, and the calculated F value is greater than F-critical. In other words, the differences among means are significant, and that the different mixtures or treatments of bricks exhibited differences in compressive strength.

Practice this method by solving more problems.

Shopping tip: See today's most popular learning promo codes and save money at popular retailers and brands. Score discounts at your favorite learning stores with exclusive offers, site-wide discount codes, and single-use codes.
Need an answer?
Get insightful answers from community-recommended
experts
in Mathematics on Knoji.
Would you recommend this author as an expert in Mathematics?
You have 0 recommendations remaining to grant today.
Comments (10)
Ranked #15 in Mathematics

This is an excellent tutorial for those new to Excel.

You have explained this well. Thank you.

very well presented...i hate stats..you know why? it's a burden for me because many of my MA and PhD classmates before keep requesting for tutorial-of course - free of charge,hehehehe

wow really nice, voted up

Although Microsoft Excel is such a powerful application, most people use it purely for making simple tables.

Thanks for the information on MS Excel, voted up.

Very detailed and informative share...displaying relevant images. This is good to know..thanks for sharing valuable information.

Wow, this is thorough and well-explained.

A great step by step tutorial.

Mj reddy

GR8 explanation thanks

ARTICLE DETAILS
RELATED ARTICLES
RELATED CATEGORIES
ARTICLE KEYWORDS