Data Matters with Excel®

Activity 8.1

Your task in this project is to find how F is distributed so you can fill in the following table. Three cells of the table are already filled in. It would be a good idea to do at least two of those first to make sure Excel is set up correctly.

Two Groups, Four Observations

The next table shows how to get started setting up Excel to collect F-values for you. (As before, until the observations are in the data, you will get “DIV/0!” marks.)

To finish setting up the table, go to column C and drag each formula down to fill its group. Then go to columns D, F, and G, in turn, and drag the formulas down to fill the rows for all observations.

Now enter into cell B2 the formula =RAND()+RAND()+RAND()+RAND()+RAND()+RAND()+
RAND()+RAND()+RAND()+RAND()+RAND()+RAND() , which will give us random draws from an approximately normal distribution. (It will be approximately normal because it is a sum of random observations.) Drag that formula down so that every observation is a draw from the same distribution. And finally, label column J F’s . Column J is the 10th column.

Make sure you understand what the worksheet is doing.

The following macro instructs Excel to calculate and save the F-values.

Sub getFValues()
  For SampleRow = 2 To 1000 ‘Edit this row to change the number of samples.
    Calculate
    Cells(SampleRow, 10).Value = Cells(2, 9).Value
  Next SampleRow
End Sub

After you have the F-values, click on J to select the column that contains them, then click on the Sort Descending button. Then you can scroll down 5% and find the cutoff.

Try a larger number of samples.

My experience is that with fewer than 10,000 samples, the F-value 5% down the list is more than 1 away from 18.56. When I was creating the entries for that table, I used more than 400,000 samples.

Other Designs

To get the cutoffs for the other sets of degrees of freedom, edit the worksheet. Add more groups and/or more observations as needed. Drag the formulas as needed. Adjust the formulas for the variances so that they are dividing by the correct degrees of freedom.

Other Distributions and Other Questions

Does it matter what the mean and/or standard deviation of your original distribution are?

Each F-value that was calculated could have been used in an ANOVA. For each of those tests, was the null hypothesis true or false? When the calculations found an F that was greater than 20, was the null hypothesis true or false? If an F above 20 were found, would we reject the null hypothesis?

What happens if you use another distribution? For example, you could replace the formula for the observations with =RAND() . Does that affect the distribution of F?

Save Your Work

You will use this workbook in the next project.