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.
Pages to are hidden for
"Random Sampling and Sample Statistics in Excel"Please download to view full document