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

##### Table of Contents

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:

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:

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.

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.

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.

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.

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.

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.

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

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

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.