Singleton Computer Assignment 2

Singleton Computer Assignment 2 complete solutions correct answers key

---- Random Numbers, Sampling and Central Limit Theorem (refers to Ch 7 & 8)

Introduction -- The Random Number Generator

Random number generation by computer allows computer simulation of random experiments. The Excel

random number generator allows the user to generate random variables which behave according to a

specified distribution. Each random value generated by Excel is another result of a chance experiment. For

example, the dialog box on the left specifies that the population mean is 0 and the population standard

deviation is 1 and the distribution shape is normal (a z distribution). It asks the program to do 3 columns of

10 random values each. This block of output is to begin at cell A1. The result on the right could be

considered a random sample of 30 values or for that matter 3 samples of 10 values each.

When you make a histogram of the random values you see a picture which approximates the theoretical

shape of the distribution, but with only 30 values there are not enough to make something that looks like a

normal distribution.

0

5

10

F r

eq u e

n c

y

Bin

Histogram

With 1000 data values the distribution looks more normal:

2

0

10

20

30

40

50

60

70

80

90

-3

-2 .6

-2 .2

-1 .8

-1 .4 -1

-0 .6

-0 .2

0 .

2

0 .

6 1

1 .

4

1 .

8

2 .

2

2 .

6 3

F r

eq u e

n c

y

Bin

Histogram

But it takes an enormous number of values to get a really smooth plot (this is 32768):

You will note that there is an empty box in the dialog form, the Random Seed. If this box is left empty,

Excel starts with a standard sequence of values, and every time you generate some more values it continues

on extending the list out with different values. However, if you put a particular integer number in this box,

the Random Number generator always starts with a particular random number corresponding to the seed, and

produces the same particular sequence every time you call it. Since computers are (when working properly)

deterministic, some people insist on saying "pseudo-random number", but this is a philosophical matter.

3

Introduction --- The Central Limit Theorem

Chapter 7 covers the Central Limit Theorem (CLT) thoroughly. This CLT provides the framework that

makes inferential statistics possible in the case of a single population sample of random variable x used to

estimate the population mean (in Chapter 8 and related work in Chapter 9). The connection between the

known and measured sample and the unknown population is provided by this theorem.

Another version of CLT is mentioned in Chapter 10 (page 352) which likewise makes the estimation and

hypothesis testing in Chapter 10 possible.

Also, there is a relationship (not specifically called CLT) involving the sampling distribution of the

variance which makes possible estimation of a confidence interval for population variance and hypothesis

testing involving the population variance. I discuss this in the Introduction to Chapter 8 in the part

"Estimating population variance".

In Computer Assignments 1, 3, and 4 for this course I ask you to do very practical and applied

computations to get practical answers. In this No. 2 assignment I just ask you to run simulations showing

how the CLT and the variance relationship work to improve your background knowledge. I will try to give

explicit instructions to make it easier. The closest thing to this assignment in our course materials is in the

WileyPLUS Chapter 7 material. It is "Interactive Applet: Understanding Sampling Distributions and the

Central Limit Theorem". If I assigned this there would be no output into a file from which I could give you a

grade, but you are welcome to try it.

Because the CLT formula

for the standard deviation of the sample mean has a square root of n in the denominator, when sample size n

is small the sample mean values are spread widely and when n is big the spread is small.

4

The theoretical distribution in the graph according to the CLT for n=4 is hugely wider than the one for n=80.

For n=4 the theoretical standard deviation of the graph is

= 1 /  = 0.5

or for n=80

= 1 / = 0.111803

Since you are taking only 1000 (250 for older Excel) instead of an infinite number of samples, you should be

able to compute values which are close but not exactly equal to these.

The formula for the theoretical distribution of the sample variance is much more complicated than in the

above case for the sample mean, so we use the chi-squared table in the book for practical work. However the

theoretical sample variance distribution shows similar behavior: (These plots are from real formula

computations.)

For a sample size of 4 the distribution is extremely skewed and wide, whereas for 80 it is relatively narrow

and very slightly skewed, almost like a normal distribution surrounding the population value.

Practical implications of this are: It would be almost meaningless to try to estimate a confidence interval

for variance (and consequently also standard deviation) with n=4, whereas with n=80, reasonable results

would be obtained. On the other hand, in some cases estimating a population mean from a sample with n=4

might be adequate. Your instructor (before having more contact with statistics) remembers that his industrial

laboratory group was warned by the company statistician when we were only using n=3 for a product test

which had to be repeated many times. We really had no idea of an estimate for the variance. The trouble

was that each reading could require a half-hour or more since the technician had to tediously manipulate a

tiny piece of fiber just right for the test to turn out at all. We later found that since the technician also had to

5

move crosshairs in the microscope to measure the fiber's thickness, she was unconsciously biasing the results

toward what we wanted (a stronger fiber).

Your Assignment

I am expecting you to be familiar with concepts discussed in "Using Excel formulas with more

sophistication".

Your assignment is to use a population with a normal distribution to generate (by Excel simulation) blocks

of 1000 different random samples. After generating 1000 samples of size n=4 and 1000 samples of size

n=80, you need to make histograms to show that the sample means and sample variances are distributed like

(have a similar shape to) the above theoretical plots. They will not look exactly like the theoretical plots

since we are only using 1000 samples, not an infinte number. Also, you need to show that the mean of

sample means and the standard deviation of sample means agree with the CLT theory. The theory says

The template for a sheet:

The final result will have 2 worksheets like this, one for n=4 and one for n=80, plus a set of histograms on

separate sheets.

1. Download the template file (note I ask you to type in the formulas into it yourself).

6

2. For n=4="Number of random numbers", generate the random values with the Random Number Generator,

Normal distribution, mean=0 and standard deviation=1, starting in cell B11. If you are using Excel 2010 or

2013 you can specify :"Number of variables"=1000. If you are using Excel 2007 or before, it cannot handle

this many and you must use 250. 1000 gives nicer histograms.

As shown the block of random values starts at cell B11 (and extends down to B14 for n=4 ).

3. Type the functions in for cells B7 through B9. (B7=mean of B11 thru B14, B8=sample standard deviation

of B11 thru B14, and B9=square of B8).

4. Select and copy cells B7 through B9, then do a multiple paste of these out to the end of the block of

random data. If you are using 1000 samples the block ends way out at column ALM. If 250, it ends out at

column IQ.

5. Now you have 1000 (or 250) trials of sample means, sample standard deviations, and sample variances.

In cell B4 compute the average of the 1000 (or 250) sample means and in B5, the standard deviation of the

1000 (or 250) sample means (use STDEV).

6. The population mean is 0 by specification (cell B1). According to the CLT the mean of the sample means

(cell B4) should be this value, but since we are estimating it with 1000 (or 250) trials it should be not exact

but close.

The theoretical mean of the sample standard deviations should also be close to the CLT theoretical formula

value. Put this in cell B2 (I have computed it for you above). This should compare closely with cell B5, your

simulated point estimate based on 1000 (or 250) trials.

7. Make a histogram of the 1000 (or 250) sample means on a new sheet. When you did Computer

Assignment 1 you used data in a column; it works the same way for data in a row. Do not bother using

"bins".

8. Also likewise make a histogram of the 1000 (or 250) sample variances on a new sheet.

9. Copy and paste the 1st 7 or so columns of your n=4 data sheet to a new blank sheet for n=80. You could

start completely over on a new blank sheet, but copying will save some work.

10. Repeat step 2 for n=80. The block of data will now extend down to row 90.

11. Repeat step 3 only now you will just have to change B14 to B90 in the average and sample standard

deviation functions.

12. Repeat step 4.

13. Repeat step 6. You have to change B2 so that it has the correct value for n=80.

14. Repeat step 7.

15. Repeat step 8.

7

As you make new sheets in your Excel file, please type in labels on their tabs so I can follow what you did.

The theoretical CLT values in cells B1 and B2 (for taking an infinite number of samples) must closely agree

with the values you got in cells B4 and B5. The histograms which you get from steps 7, 8, 14, and 15 must

resemble the corresponding distribution plots above. The numbers on the vertical axis will be different since

you are drawing frequency plots, and the theories are in terms of probability densities. But the shapes should

be approximately the same, and the numbers on the horizontal axis should correspond. Needless to say your

histograms will be smoother for 1000 trials than if you had to use Excel 2003 with 250 trials.

Note that students may get various different results depending on how they happened to use the random

number generator. I grade by the general appearance of the results, not the exact number values.

Singleton Computer Assignment 2 complete solutions correct answers key

---- Random Numbers, Sampling and Central Limit Theorem (refers to Ch 7 & 8)

Introduction -- The Random Number Generator

Random number generation by computer allows computer simulation of random experiments. The Excel

random number generator allows the user to generate random variables which behave according to a

specified distribution. Each random value generated by Excel is another result of a chance experiment. For

example, the dialog box on the left specifies that the population mean is 0 and the population standard

deviation is 1 and the distribution shape is normal (a z distribution). It asks the program to do 3 columns of

10 random values each. This block of output is to begin at cell A1. The result on the right could be

considered a random sample of 30 values or for that matter 3 samples of 10 values each.

When you make a histogram of the random values you see a picture which approximates the theoretical

shape of the distribution, but with only 30 values there are not enough to make something that looks like a

normal distribution.

0

5

10

F r

eq u e

n c

y

Bin

Histogram

With 1000 data values the distribution looks more normal:

2

0

10

20

30

40

50

60

70

80

90

-3

-2 .6

-2 .2

-1 .8

-1 .4 -1

-0 .6

-0 .2

0 .

2

0 .

6 1

1 .

4

1 .

8

2 .

2

2 .

6 3

F r

eq u e

n c

y

Bin

Histogram

But it takes an enormous number of values to get a really smooth plot (this is 32768):

You will note that there is an empty box in the dialog form, the Random Seed. If this box is left empty,

Excel starts with a standard sequence of values, and every time you generate some more values it continues

on extending the list out with different values. However, if you put a particular integer number in this box,

the Random Number generator always starts with a particular random number corresponding to the seed, and

produces the same particular sequence every time you call it. Since computers are (when working properly)

deterministic, some people insist on saying "pseudo-random number", but this is a philosophical matter.

3

Introduction --- The Central Limit Theorem

Chapter 7 covers the Central Limit Theorem (CLT) thoroughly. This CLT provides the framework that

makes inferential statistics possible in the case of a single population sample of random variable x used to

estimate the population mean (in Chapter 8 and related work in Chapter 9). The connection between the

known and measured sample and the unknown population is provided by this theorem.

Another version of CLT is mentioned in Chapter 10 (page 352) which likewise makes the estimation and

hypothesis testing in Chapter 10 possible.

Also, there is a relationship (not specifically called CLT) involving the sampling distribution of the

variance which makes possible estimation of a confidence interval for population variance and hypothesis

testing involving the population variance. I discuss this in the Introduction to Chapter 8 in the part

"Estimating population variance".

In Computer Assignments 1, 3, and 4 for this course I ask you to do very practical and applied

computations to get practical answers. In this No. 2 assignment I just ask you to run simulations showing

how the CLT and the variance relationship work to improve your background knowledge. I will try to give

explicit instructions to make it easier. The closest thing to this assignment in our course materials is in the

WileyPLUS Chapter 7 material. It is "Interactive Applet: Understanding Sampling Distributions and the

Central Limit Theorem". If I assigned this there would be no output into a file from which I could give you a

grade, but you are welcome to try it.

Because the CLT formula

for the standard deviation of the sample mean has a square root of n in the denominator, when sample size n

is small the sample mean values are spread widely and when n is big the spread is small.

4

The theoretical distribution in the graph according to the CLT for n=4 is hugely wider than the one for n=80.

For n=4 the theoretical standard deviation of the graph is

= 1 /  = 0.5

or for n=80

= 1 / = 0.111803

Since you are taking only 1000 (250 for older Excel) instead of an infinite number of samples, you should be

able to compute values which are close but not exactly equal to these.

The formula for the theoretical distribution of the sample variance is much more complicated than in the

above case for the sample mean, so we use the chi-squared table in the book for practical work. However the

theoretical sample variance distribution shows similar behavior: (These plots are from real formula

computations.)

For a sample size of 4 the distribution is extremely skewed and wide, whereas for 80 it is relatively narrow

and very slightly skewed, almost like a normal distribution surrounding the population value.

Practical implications of this are: It would be almost meaningless to try to estimate a confidence interval

for variance (and consequently also standard deviation) with n=4, whereas with n=80, reasonable results

would be obtained. On the other hand, in some cases estimating a population mean from a sample with n=4

might be adequate. Your instructor (before having more contact with statistics) remembers that his industrial

laboratory group was warned by the company statistician when we were only using n=3 for a product test

which had to be repeated many times. We really had no idea of an estimate for the variance. The trouble

was that each reading could require a half-hour or more since the technician had to tediously manipulate a

tiny piece of fiber just right for the test to turn out at all. We later found that since the technician also had to

5

move crosshairs in the microscope to measure the fiber's thickness, she was unconsciously biasing the results

toward what we wanted (a stronger fiber).

Your Assignment

I am expecting you to be familiar with concepts discussed in "Using Excel formulas with more

sophistication".

Your assignment is to use a population with a normal distribution to generate (by Excel simulation) blocks

of 1000 different random samples. After generating 1000 samples of size n=4 and 1000 samples of size

n=80, you need to make histograms to show that the sample means and sample variances are distributed like

(have a similar shape to) the above theoretical plots. They will not look exactly like the theoretical plots

since we are only using 1000 samples, not an infinte number. Also, you need to show that the mean of

sample means and the standard deviation of sample means agree with the CLT theory. The theory says

The template for a sheet:

The final result will have 2 worksheets like this, one for n=4 and one for n=80, plus a set of histograms on

separate sheets.

1. Download the template file (note I ask you to type in the formulas into it yourself).

6

2. For n=4="Number of random numbers", generate the random values with the Random Number Generator,

Normal distribution, mean=0 and standard deviation=1, starting in cell B11. If you are using Excel 2010 or

2013 you can specify :"Number of variables"=1000. If you are using Excel 2007 or before, it cannot handle

this many and you must use 250. 1000 gives nicer histograms.

As shown the block of random values starts at cell B11 (and extends down to B14 for n=4 ).

3. Type the functions in for cells B7 through B9. (B7=mean of B11 thru B14, B8=sample standard deviation

of B11 thru B14, and B9=square of B8).

4. Select and copy cells B7 through B9, then do a multiple paste of these out to the end of the block of

random data. If you are using 1000 samples the block ends way out at column ALM. If 250, it ends out at

column IQ.

5. Now you have 1000 (or 250) trials of sample means, sample standard deviations, and sample variances.

In cell B4 compute the average of the 1000 (or 250) sample means and in B5, the standard deviation of the

1000 (or 250) sample means (use STDEV).

6. The population mean is 0 by specification (cell B1). According to the CLT the mean of the sample means

(cell B4) should be this value, but since we are estimating it with 1000 (or 250) trials it should be not exact

but close.

The theoretical mean of the sample standard deviations should also be close to the CLT theoretical formula

value. Put this in cell B2 (I have computed it for you above). This should compare closely with cell B5, your

simulated point estimate based on 1000 (or 250) trials.

7. Make a histogram of the 1000 (or 250) sample means on a new sheet. When you did Computer

Assignment 1 you used data in a column; it works the same way for data in a row. Do not bother using

"bins".

8. Also likewise make a histogram of the 1000 (or 250) sample variances on a new sheet.

9. Copy and paste the 1st 7 or so columns of your n=4 data sheet to a new blank sheet for n=80. You could

start completely over on a new blank sheet, but copying will save some work.

10. Repeat step 2 for n=80. The block of data will now extend down to row 90.

11. Repeat step 3 only now you will just have to change B14 to B90 in the average and sample standard

deviation functions.

12. Repeat step 4.

13. Repeat step 6. You have to change B2 so that it has the correct value for n=80.

14. Repeat step 7.

15. Repeat step 8.

7

As you make new sheets in your Excel file, please type in labels on their tabs so I can follow what you did.

The theoretical CLT values in cells B1 and B2 (for taking an infinite number of samples) must closely agree

with the values you got in cells B4 and B5. The histograms which you get from steps 7, 8, 14, and 15 must

resemble the corresponding distribution plots above. The numbers on the vertical axis will be different since

you are drawing frequency plots, and the theories are in terms of probability densities. But the shapes should

be approximately the same, and the numbers on the horizontal axis should correspond. Needless to say your

histograms will be smoother for 1000 trials than if you had to use Excel 2003 with 250 trials.

Note that students may get various different results depending on how they happened to use the random

number generator. I grade by the general appearance of the results, not the exact number values.