How to Perform Systematic Sampling in Excel

This guide will discuss how to perform systematic sampling in Excel.

Since Excel contains several tools and functions, many people use it to organize and analyze data. And it is also an excellent tool to store and organize data results from a population.

When we conduct research, we will often use a sample to be used rather than the entire population. So there are many different methods for getting the appropriate sample from a population. 

Additionally, we often use a probability sampling method to get a sample from a population to use in research or calculation. And one type of probability sampling is systematic sampling which is used to select members of a certain population at a regular interval determined in advance. 

If we have a random or random-like population, we can use systematic sampling to obtain a representative sample which we can use to conclude the population. 

Furthermore, we can easily perform systematic sampling in Excel. With the help of the built-in functions available in Excel, systematic sampling can quickly be performed. For example, we can utilize the RANDBETWEEN function, COUNT function, and ROUNDDOWN function when we perform systematic sampling. 

Let’s take a sample scenario wherein we need to perform systematic sampling in Excel. 

Suppose you have a population list of students and their late and absences. And you want to conduct a study of the student’s attendance. So you want to use a sample of the population to draw conclusions. To make the process easier, you opted to perform a systematic sampling method in Excel.

Great! Before we begin, let’s discuss how to write the RANDBETWEEN function in Excel. 

 

The Anatomy of the RANDBETWEEN Function

The syntax or the way we write the RANDBETWEEN function is as follows:

=RANDBETWEEN(bottom, top)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • RANDBETWEEN() refers to our RANDBETWEEN function. And this function is used to return a random number between the numbers we specify.
  • bottom is a required argument. And it refers to the smallest integer the function will return. 
  • top is also a required argument. So it refers to the largest integer the function will return.

Now let’s learn how to use the ROUNDDOWN function in Excel. 

 

The Anatomy of the ROUNDDOWN Function

The syntax or the way we write the ROUNDDOWN function is as follows

=ROUNDDOWN(number, num_digits)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • rounddown() refers to our ROUNDDOWN function. And this function is used to round a number down toward zero.
  • number is a required argument. And it refers to any real number we want to round down. 
  • num_digits is another required argument. So it refers to the number of digits which we want to round. So negative value rounds to the left of the decimal points. If zero or omitted, it rounds to the nearest integer. 

Amazing! Now let’s dive into a real example of performing systematic sampling in Excel. 

 

A Real Example of Performing Systematic Sampling in Excel

Let’s say we have a population list containing the student IDs and the number of times the student has been late and absent. So our initial data set would look like this:

Initial data set

 

So a commonly used sampling method is systematic sampling. And this method is very similar to simple random sampling. But, an advantage of using systematic sampling is we can still use it even if we don’t have the population list in advance. 

Additionally, systematic sampling considers the order in which our population list is arranged to ensure the sample is valid. For example, our population list is arranged alphabetically. 

Since systematic sampling will include data from both the bottom and top ends of the population list, we will have a fairly representative sample. Essentially, systematic sampling places each member of the population in some order. 

Then, it will choose a random starting point and select every nth member to be part of the sample. 

Firstly, we need to have the population list in Excel. Then, we need to decide on a sample size. For example, we want our sample size to be n = 5. Next, we need to calculate the population size, the step size, and the random starting point for our systematic sampling. 

So the RANDBETWEEN function will return a random value to be the starting point in our data set. Then, the ROUNDDOWN function will calculate the step size value for our data set. Lastly, we can use the COUNT function to obtain the population size. 

Afterward, we need to label each data value based on the results of our calculations. Once we have labeled all the values in the data set, we need to filter the data set to only display the values with the label 1. Finally, we will only have a data set representing our sample size of 5. 

So our final data set would look like this:

Final result

 

You can make your own copy of the spreadsheet above using the link attached below. 

Finally, we can discuss the steps of how to perform systematic sampling in Excel.

 

 

How to Perform Systematic Sampling in Excel

In this section, we will explain the step-by-step process of how to perform systematic sampling in Excel.

1. Firstly, we need to input the parameter values we will use for our systematic sampling. In this case, we have decided to have a sample size of n = 5. Next, we will calculate the population size. To do this, we can simply input the formula “=COUNT(B2:B11)”. Then, press the Enter key to return the value.

Calculating the population size

 

2. Secondly, we will calculate the step size for our data set. And we will be using the ROUNDDOWN function. So type in the formula “=ROUNDDOWN(G2/G1, 0)”. Lastly, press the Enter key to return the result.

Calculating the step size

 

3. Thirdly, we need to get the starting point for our data set using the RANDBETWEEN function. In this case, we will input the formula “=RANDBETWEEN(1, 10)”. Then, press the Enter key to return the result.

Systematic Sampling in Excel

 

4. Next, we will label each value in the data set. Since the RANDBETWEEN function randomly chose the value 3, this will be our starting point. Thus, we will start inputting label 1 on cell E4. Then, the number is down to the step size of 2. Afterward, we can input “=E4” in the next cell.

Systematic Sampling in Excel

 

5. Then, we can simply drag down the Fill Handle to input the labels. But, we do need to manually fill in the values at the top of the starting point.

Systematic Sampling in Excel

 

6. Lastly, we need to filter the values to only display the data values with the label 1. To do this, we will go to the Data tab and click the Filter icon.

Systematic Sampling in Excel

 

7. Next, we will click the Labels column and filter to only include the rows that have a label with the value of 1.

Systematic Sampling in Excel

 

8. And tada! We have successfully performed systematic sampling in Excel and obtained a sample size of 5.

Systematic Sampling in Excel

 

And that’s pretty much it! We have explained how to perform systematic sampling in Excel. Now you can use this method in Excel whenever you need to get a sample from a population quickly and easily. 

Are you interested in learning more about what Excel can do? You can now use the RANDBETWEEN function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like