When done, close browser tab or window.
Start video.
McGraw Hill red cube logo. Because learning changes everything™
Title screen: Generating a simple random sample without replacement. Copyright ©2017 McGraw-Hill Education. All rights reserved.
Narrator: "This video will demonstrate how to draw a simple random sample without replacement from a data set using the RAND, INDEX, and RANK functions. Excel does not have a built-in function that samples without replacement, but we can easily build a formula to do it for us. This data set lists the 50 states in alphabetical order. A number has been assigned to each state from 0 to 49. I've added a column called Rand. In this column, I'm going to generate a set of 50 random numbers, and then I'll drag it down the column."
Equals Rand left parenthesis right parenthesis is entered to cell C2. The bottom right corner of the cell is dragged down to row 51 to automatically copy and paste the formula.
Narrator: "I've now generated a set of random numbers between 0 and 1. I'm going to copy and Paste Special."
Within the Clipboard section of the Home tab, Paste is expanded to select the first option under Paste Values. Text: To freeze the random values, copy the column, then with the cells selected, click the Paste dropdown and paste as 'Values.'
Narrator: "Now I have fixed randomly-generated numbers, which I can use in my formula. This is the formula I'm going to put in column 'D.' You may wish to pause the video and copy it down."
The formula: equals Index left parenthesis $A$2:$A$51 comma Rank left parenthesis C2 comma $C$2:$C$51 right parenthesis right parenthesis.
Narrator: "Now let's build the formula in the cell and talk it through from the inside out. =INDEX. I'm going to select all of the states that are in my range, and I'm going to lock them with the F4 key. RANK comes next. And I'm going to select cell C2, which points at the first value in my Rand column. And then I need to select my 'C' column. And I'm going to lock those cells with F4. Close parenthesis, and close parenthesis again, and now I'll hit 'Enter.'"
The full formula in cell D2: equals index left parenthesis $A$2:$A$51 comma rank left parenthesis C2 comma $C$2:$C$51 right parenthesis right parenthesis.
Narrator: "And let's talk about what we've just done. The RANK part of this formula says, 'Go look at the number in the first cell,' in the cell I specified, which is C2. 'Where does that number fall in the range from C2 to C51?' And Excel will rank the numbers in column 'C' from highest to lowest and figure out where this value fits."
The randomly generated number in cell C2, currently 0.4909342, is circled.
Narrator: "Let me copy these numbers over to another column. And now I'm going to do a sort from largest to smallest. And let's find where this one is in my list. . 4909. Right here."
With cell C2 selected, the narrator applies a white fill to the cell with the fill button found in the font section of the home tab. The duplicate number is found in cell F27, and a white fill is also applied.
Narrator: "That is how many down the list? 26 down the list. So, over in column 'A,' now that we know it's 26 down the list, the INDEX portion of this formula says, 'Go to column 'A' and go 26 down the list and pick that choice.' So, here we are. You can see it's counting down at the bottom."
Cells C2 to C27 are highlighted to view the count of 26 at the bottom of the screen, next to average and sum.
Narrator: "26 is number 25, Montana, and that's what's put right here. So our Random Draw is now state number 25, Montana. I'll drag this down to row 11, which will give me 10 randomly-drawn items from my list of 50."
Cell D2 is dragged to C11 to automatically copy and paste the formula.
Narrator: "So we have a list of 10 unique items with no duplicates. And that is how we can get a simple random sample without replacement from a data set. In this video, we have demonstrated how to use RAND, INDEX, and RANK functions to generate a simple random sample without replacement."
Video has ended.
Described transcript ©2023 McGraw Hill. All rights reserved. No reproduction or further distribution permitted without the prior written consent of McGraw Hill.
Close browser tab or window.