Docstoc

Random Sampling and Sample Statistics in Excel

Document Sample
Random Sampling and Sample Statistics in Excel Powered By Docstoc
					Computer Exercise I
Taking a SRS using Excel.
Random Sampling and Sample Statistics in Excel
In this exercise we will use random number generation to take a random sample from
population. We begin by entering the data into the first column:




Suppose this data represents the students in your class. We have a population of size 26,
and we are going to take an individual sample of size 8. Next we assign random numbers
to each individual. First make sure that the Analysis Tool-Pak add-inn is activated. To
do this select “Add-ins..” from the “Tools” Menu on the menu bar. This will allow us to
use a Random Number Generator.
Then under Tools, bring up the Data Analysis menu. Select Random Number Generation
from the list.




We want 26 random numbers (1 for each unit in our frame), uniform distribution, and we
want the output range to be the cells immediately to the right of our data “$B2:$B27”.
You should put a label in the cell above this range. To select the output range, click on
the red arrow to the right of the Output Range text box.
This will shrink the dialogue box and allow you to select the appropriate cells. Then
press the red arrow again to return to the dialogue.

The seed is needed to start the process that makes the random numbers. However, these
are not truly random, because the same non-zero seed will give the same sequence of
numbers each time. They do however have the property that they have no discernable
pattern to them. Furthermore, if we enter no seed or a seed of 0, Excel takes its seed from
the very precise digits of the computer’s clock. This simulates randomness since these
numbers should not be the same each time we use the Generator. Use the generator twice
with 0 and twice with another number to see what I mean. Here I have chosen 7 as our
seed.




Once we have our random numbers, how does this help us take a random sample? If the
numbers are random, then if we sort the individuals by these numbers, we should get a
random ordering of the individuals.
Using the Sort command on ALL of the data allows us to shuffle the rows around, and if
you have labels on your columns it is easy to select the option to sort by the random
number.
Now that we have sorted the data, we see that individuals A, Q, U, K, P, F, D, and V have
been selected, because they are the first eight on the new list.




Now it is your turn to use some real data. It is important that we all use the SAME SEED
in generating our random numbers: 5. Also remember to set the distribution of the
Generator as “Uniform”. This way we will all get the same answers and it will make the
homework easier to grade objectively. The following is a list of initials of four sections
of STA 291.
Student    Random #
CEA
IA
SWA
JMB
KMB
LCB
KAB
EKB
IB
JRB
AJB
JAB
SBC
CAC
BJC
MEC
JTC
CRC
CMC
TGD
SDD
RCD
MRD
HAD
ACD
JCE
JCF
CF
LRF
ADG
MJG
EKH
AWH
SMH
KMH
JWH
ETH
LCH
VRH
KMJ
SNJ
LAK
AMK
JPK
SRK
REK
RLK
MAK
HDL
SML
JGL
BAL
RJM
KM
JBM
RAM
JEM
WLN
JMO
ADS
Take a random sample of size 10 from the 60 members of the population. Report the
student’s initials in the sample.