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

Knoji reviews products and up-and-coming brands we think you'll love. In certain cases, we may receive a commission from brands mentioned in our guides. Learn more.
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.

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.

Guest
Posted on Jan 26, 2012
Mark Feldt
0
Posted on Jan 16, 2012
Christy Birmingham
0
Posted on Jan 16, 2012
Donata L.
0
Posted on Jan 15, 2012
Account Deletion Requested
0
Posted on Jan 15, 2012
New Energy
0
Posted on Jan 14, 2012
tamrh alan
0
Posted on Jan 14, 2012
Nobert Bermosa
0
Posted on Jan 13, 2012
Roberta Baxter
0
Posted on Jan 13, 2012
Jerry Walch
0
Posted on Jan 13, 2012