Randomly Extract a Certain Percentage of Rows in Google Sheets

GiGiKing·March 13, 2024·0 comments

Are you looking for a way to randomly extract a certain percentage of rows in Google Sheets? Whether it’s selecting 10% of data from a large number of form submissions or any other similar scenario, I’ve got you covered! In this article, I will provide you with two solutions – one fully formula-oriented and the other more user-friendly. Let’s dive in!

The Formula to Randomly Extract a Certain Percentage of Rows in Sheets

Let’s start with the fully formula-based solution. The logic behind this formula is as follows:

Generate a set of random numbers using the RANDBETWEEN function in a unique way.
Use these random numbers to sort the data.
Calculate the percentage of rows to extract.
Constrain the number of rows using Array_Constrain or Query.

To better understand the process, let’s work with a sample dataset. Here it is:

[Image: Randomly Extract a Certain Percentage of Rows](https://updf.com/wp-content/uploads/2023/05/copy-the-google-sheets-data.jpg)

Step #1: Generating a Set of Unique Random Numbers

To generate unique random numbers in all the non-blank rows, you can use the following formula in Google Spreadsheets:

=ArrayFormula(if(len(A2:A),randbetween(row(A1:A),9^9),))

This formula generates unique numbers between 1 and 387420489 (9^9) for each non-blank cell in the range. In our case, it will generate 13 random numbers.

Step #2: Sorting the Data in Random Order

Now, let’s sort the data randomly using the unique random numbers we generated in Step #1. You can use the SORT function as follows:

=sort(A2:B,if(len(A2:A),randbetween(row(A1:A),9^9),),0)

This formula will randomly sort the data in Google Sheets.

Step #3: Finding a Certain Percentage of Total Data

To determine the desired percentage of rows to extract, you need to consider the total number of rows in the dataset. Let’s say you want to extract 50% of the rows in the range A2:B14. You can use the following formula:

=round(counta(A2:A)*0.5)

Alternatively, you can use the PERCENTILE function as well:

=ArrayFormula(PERCENTILE(if(LEN(A2:A),row(A1:A),),0.5))

Step #4: Constraining the Number of Rows

Finally, let’s combine all the formulas to extract the desired percentage of rows. Here’s the formula:

=array_constrain(sort(A2:B,if(len(A2:A),randbetween(row(A1:A),9^9),),0),round(counta(A2:A)*0.5),2)

By replacing the “input_range” with the formula from Step #2 and “num_rows” with the formula from Step #3, you will randomly extract a certain percentage of rows in Google Sheets.

Pros and Cons of the Formula-based Solution

Before we move on to the second solution, let’s discuss the pros and cons of the formula-based approach.

Pros:

With this formula, you can easily copy and extract a certain percentage of rows in Google Sheets.

Cons:

The formula output will be randomized each time you make changes to your sheet due to the volatile nature of the RANDBETWEEN function.

To avoid unintended changes, make sure to copy and paste the values instead of keeping the formula.

Randomizing the Range and Copying a Certain Percentage of Rows

Now, let’s explore a more user-friendly solution using the “Randomize Range” option in the data menu. The steps are quite similar to the previous solution.

Here is the formula to randomly copy a certain percentage of rows:

=array_constrain(A2:B,round(counta(A2:A)*0.5),2)

To use this formula effectively, follow these steps:

Select the data range A2:B14.
Go to the “Data” menu and select “Randomize range.”
Apply the formula above to any blank cell.

That’s it! This method allows you to conveniently extract a certain percentage of rows in Google Sheets. Let’s take a quick look at its pros and cons.

Pros:

It improves the performance of Google Sheets.
The formula output won’t change automatically. It will only update when you make changes in the source data.

Cons:

This method requires manual randomization of the range, making it semi-automatic.

That’s all for now! Enjoy randomly extracting a certain percentage of rows in Google Sheets using these methods. If you need further assistance, visit Crawlan.com, where you can find more helpful tips and tricks.

Additional Resources: