Table of Contents
Stratified sampling is a technique used to ensure that each member of a population has an equal chance of being selected for a sample. To perform stratified sampling in Excel, first divide your population into separate groups based on a shared characteristic. Then, create a separate column for each group. Next, use the RAND function to randomly select a sample size from each group. Finally, use the COUNTIF function to ensure that the sample size from each group is equal to the sample size you wanted.
In statistics, we often take samples from a population and use the data from the sample to draw conclusions about the population as a whole.
One commonly used sampling method is stratified random sampling, in which a population is split into groups and a certain number of members from each group are randomly selected to be included in the sample.
The following step-by-step example shows how to perform stratified random sampling in Excel.
Step 1: Enter the Data
First, let’s enter the following dataset into Excel:
Next, we’ll perform stratified random sampling in which we randomly select two players from each basketball team to be included in the final sample.
Step 2: Enter Random Values for Each Row
Next, let’s create a new column titled Random and type in =RAND() for the first value:
This generates a random value between 0 and 1.
Next, hover over the bottom right corner of the cell until a tiny cross ( + ) appears and double click it to paste the =RAND() formula to all remaining cells in the column.
Unfortunately, each time we hit Enter the random cell values will change. To prevent this, copy every value in column E then right click and choose Paste Values into the same column so that the random values will no longer change.
Step 3: Sort Data Values
Next, highlight all of the data. Then click the Data tab along the top ribbon. Then click the Sort button within the Sort & Filter group.
In the new window that appears, sort first by Team from A to Z, then sort by Random from Smallest to Largest.
Once you click OK, the data will be sorted accordingly.
Step 4: Select the Final Sample
The final sample will simply be the first two rows from each team:
The final sample will include the following player ID’s from each team:
- Team A: 3, 5
- Team B: 9, 6
- Team C: 15, 11
Our stratified random sampling is complete because we have now chosen two players from each team.
The following tutorials explain how to select other types of samples from a population using Excel: