Stats Help
Computer Assignment # 3 (Illustration of Central Limit Theorem)
Instructions
As you do this exercise, remember that neatness counts. If you type something into a cell, you mayneed to resize the cell to make it readable (by me). Complete the instructions as written. If I expect you to answer a question on the spreadsheet, I will tell you in which cell to write the answer. Remember to save your work often.
What are we trying to do?
The intent of this exercise is to observe firsthand the impact of the Central Limit Theorem: under repeated sampling, the distribution of a sample mean () has a normal distribution. We will do this by sampling n = 36 values from a continuous uniform random variable, X, on [0, 10] and observing the characteristics of
.
(1) Open a new file in Excel. Rename the first worksheet of that file Population. Fill in your worksheet as shown below.
Next use the following formulas for uniform distributions to fill in B6:B7.
Use an appropriate command to find the standard deviation of x in B8.
Next fill in the values of the mean, variance, and standard deviation of in C6:C8 using formulas involving B6:B8and the facts
E[] = E[X]
Var() = Var(X)/n
*****Save your work.***** (Save using your “last name_Comp3” as the file name)
At this point, you know the mean, variance, and standard deviation of two populations: (1) the population from which we will be sampling, which has a uniform distribution on [0,10]; and (2) the population of , which should have an approximate normal distribution according to the Central Limit Theorem.
(2) Select a new worksheet and rename it Samples. We will use the first row for labels. In A1 write “Samples”. In B1:AK1 write the labels X1, X2, … X36. These values represent the 1st, 2nd, …,36th values in our sample. In AL1 write X-Bar. Underline the labels (by right-clicking on the cells and choosing Format Cells – Border to outline only the bottom of the cells).
(3) Returning to A2, we type “1” to indicate this is our first sample. So how do we perform our sample?
As shown above and as I illustrated in class, Excel has a function entitled RAND() which will automatically generate a value uniformly distributed on [0,1]. This function does not have arguments, but you do have to use opened and closed parentheses after its name. To get a value uniformly distributed on [0,10], you simply have to compute
=10*rand()
If you type this in B2, you can copy it to cells C2:AK2 to get our first sample of size 36. In AL2, compute the average of these 36 values. Be sure not to include the number in A2.
All of your sample values as well as the sample mean should be between 0 and 10. Hit F9 (recalculate) a few times to observe different samples being selected.
*****Save your work.*****
(Note: We are about to create 1000 samples like the one above and build some frequency distributions based on our results. The function RAND() will recalculate every time something new is added to the spreadsheet. Excel does this quickly, and it is not a problem to your work. Some people find this feature of Excel unnerving. You may choose to stop the automatic recalculation if you like, but you do not need to disable it if this doesn’t bother you.If you would like to stop the automatic recalculation, choose File and then Options (second from the bottom of this menu) Now select the second option called Formulas.
Select the Manual button. You can use the same procedure to turn automatic recalculation on later. Important point—since you turned off the automatic recalculation, you will not see the system respond as you are accustomed. You can and should hit F9 to make the system recalculate to check your calculations as you go through this exercise.)
(4) Number the samples 2-1000 in A3:A1001. One way to do this is to type “2” in A3, select A2:A3, and copy.
(5) Copy the cells in B2:AL2 to rows 3:1001. If you turned off the automatic recalculation, the samples look exactly the same. Press F9.
The 1000 values in AL2:AL1001 are 1000 sample means computed by taking samples of size 36 from a population uniformly distributed on [0,10].
*****Save your work.*****
(6) Next we want to look at the values of the sample means and verify that they seem to be coming from a normal distribution with a mean of 5.0 and a standard deviation of 0.481. In AN1 type “X-bar Avg”. In AN2 type “X-bar Stdev”. In AO1, compute the mean for the 1000 X-bar values. In AO2, compute the standard deviation of the 1000 X-bar values found in AL2:1001. Do your values look like what we expect?
(7) Now we will construct a frequency distribution of the X-bar values. Go back to the Population worksheet and inE1 type “No. ofStdDev”. In E2:15 type the numbers: -3, -2.5, -2, -1.5, -1, -0.5, 0, .5, 1, 1.5, 2, 2.5, 3, 10.4. In F1 type the label “Bins”. In F2 perform the calculation
=$C$6+E2*$C$8
Copy this calculation to F3:F15.
In G2, describe what the number in F2 means, using your knowledge of what the values in C6 and C8 are.Notice that the last value, 10.4, was chosen so that the last bin would contain the largest possible sample means, which cannot exceed 10.
Select E1:F15 and select copy. Return to the Samples worksheet and go to cell AQ1. Under Paste, select Paste Special and select values as shown below.
Reduce the number of decimal places used under “Bins” to 2.
*****Save your work.*****
(8) In AS1 type “Cumulative”. In AS2 type
=FREQUENCY($AL$2:$AL$1001,AR2)
Recall from our first laptop exercise that the frequency function takes an array as the first argument and returns the count of all values smaller than the second argument. Copy this function to AS3:AS15.
(9) In AT1 type “Frequency”. Use the cumulative frequencies in column AS to create the bin frequencies in AT2:AT15.
(10) In AU1 type “Normal Prob”. Use the function normsdist and the number of standard deviations in column AQ to construct the cumulative standard normal distribution in AU2:AU15. For example, AU2 would appear as
=NORMSDIST(AQ2)
Copy this calculation to AU3:AU15.
(11) In AV1 type “Expected Cumulative”. Multiply each of the values in column AU by 1000. For example, AV2 would appear as
=1000*AU2
The values in column AV represent the number of times in our production of 1000 sample means we expect to see a sample mean fall below the bin value in column ARif the sample means are normally distributed.
(12) In AW1 type “Expected Frequency”. Use the cumulative frequencies in column AV to compute the expected frequencies for each bin. (Note: this computation is exactly the same computation you did in constructing column AT.)
*****Save your work.*****
(13) Graph the frequencies in column AT and the expected frequencies in column AW using a column chart. The x-axis should be labeled with values corresponding to the number of standard deviations away from the mean (the values found in column AQ). To format the x-axis in this manner, click one of the x-axis values so the entire set of values is selected. Then right click and choose “select data.” A box will appear which asks you to select the data source. On the right-hand side, you will see an option for “Horizontal (Category) Axis Labels.”Under this, click “Edit” and then select the corresponding data in column AQ. Be sure to label the axes on your graph and give the chart an appropriate title as well.
*****Save your work.*****
Using your graph, compare the frequencies with the expected frequencies. Now you can hit F9 to resample. Do this several times. Visually, do the values of appear to be coming from a normal distribution?
