#STRATIFIED RANDOM SAMPLING HOW TO#

In IF function logical_test is the formula E3>ROUND(COUNTIFS(Product_Group,B3)/COUNTA(Product_Group)*Sample_Size,0) while the parameters value_if_true and value_if_false are the strings “No” and “Yes”. =1+COUNTIFS(Product_Group,B3,Random_Number,'ROUND(COUNTIFS(Product_Group,B3)/COUNTA(Product_Group)*Sample_Size,0),'No','Yes') In column E we will place the formula to get the “Product ID” rank in each group: Using the RAND formula to get a random value between 0 and 1 Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.įigure 6.To apply the formula, we need to follow these steps: The randomized values are placed in column D and will help us to get the stratified random sample in column F. The RAND function gets the random value from 0 to 1. Creating a named range Sample_Size for column “Sample Size” Creating a named range Random_Number for column “Random Number”įigure 5. Creating a named range Product_Group for column “Product Group”įigure 4. Write the name for the cell range and press enterįigure 3.Select the cell range that should be named.To create a named range we should follow the steps: In order to make the formula more clear, we will create a named range Product_Group for cell range B3:B11, Random_Number for cell range D3:D11 and a named range Sample_Size for the cell H3.

The population is divided into groups and the number of samples from each group is defined by group share in the entire population. The stratified random sampling is a way of creating the sample based on the groups share in the entire population. We want to create a stratified random sample of the data in column C based on the “Product Group” and “Sample Size”. Data for the example Create a Stratified Random Sample with RAND, COUNTIFS, IF, ROUND and COUNTA Functions The idea is to create a stratified random sample based on the product groups, product ID and the sample size.įigure 2. The second table has one column “Sample Size” (column H). Our first table consists of 5 columns: “Product Group” (column B), “Product ID” (column C), “Random Number” (column D), “Rank in Group” (column E) and “Sample” (column F). Setting up Our Data to Create a Stratified Random Sample in Excel range – a range of cells where we want to count non-blank cells.num_digits – a number of decimal places to which we want to round a number.number – a decimal number which we want to round.The parameters of the ROUND function are: value_if_false – a value which the function returns if a logical_test is FALSE.value_if_true – a value which the function returns if a logical_test is TRUE.logical_test – a logical expression that we want to check.=IF(logical_test, value_if_true, value_if_false) criteria1, criteria2 – a criteria in criteria ranges which we want to count.criteria_range1, criteria_range2 – ranges where we want to apply our criteria.The parameters of the COUNTIFS function are: =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2. The function returns a random decimal number between 0 and 1 and has no parameters. The generic formula for the RAND function is:

Create a stratified random sample Excel Syntax of the RAND Formula

This step by step tutorial will assist all levels of Excel users to create a random sample of the population based on the groups share in the populationįigure 1.

#STRATIFIED RANDOM SAMPLING HOW TO#

How to Create a Stratified Random Sample in ExcelĮxcel allows us to create a stratified random sample using the RAND, COUNTIFS, IF, ROUND and COUNTA functions.

Stratified random sampling