Are you struggling to pick random values based on a specific condition in Google Sheets? Don’t worry, I’ve got you covered! In this article, I’ll walk you through the suitable functions to achieve this and provide you with practical examples.
The Functions You Need to Know
When it comes to picking random values in Google Sheets based on a condition, there are three functions you can consider: RAND, RANDBETWEEN, and RANDARRAY. However, none of these functions support criteria as an argument. But don’t fret, we can still achieve our goal by combining these functions with others.
How to Pick Random Values with Condition in Google Sheets
To pick random values based on a condition in Google Sheets, we can use a combination of either RANDBETWEEN or RANDARRAY with the FILTER function. Let’s dive into some examples to illustrate this.
Let’s assume we have a two-column dataset. The first column contains dates, and the second column contains corresponding weekday/weekend information. We want to randomly select dates based on a specific weekday.
Option 1: Using RANDBETWEEN and FILTER
In this example, we’ll extract random dates where the day number falls on a Sunday. Follow these steps to achieve the desired results:
- In cell D2, enter the criteria (Sunday in this case).
- Use the FILTER function to filter the dates based on the weekday/weekend criteria. The formula would be:
=FILTER(A2:A32, B2:B32=D2). This will return a filtered list of dates. - Next, we need to generate random numbers to sort the filtered dates randomly. We can use the RANDBETWEEN function for this. The formula would be:
=ArrayFormula(Array_constrain(RANDBETWEEN(Row(A1:A1000)^1, 1000), COUNTA(FILTER(A2:A32, B2:B32=D2)), 1)). This will generate random numbers limited to the number of dates that meet the criteria. - Finally, sort the filtered dates using the random numbers. The formula would be:
=SORTN(FILTER(A2:A32, B2:B32=D2), COUNTA(FILTER(A2:A32, B2:B32=D2)), 0, Array_constrain(RANDBETWEEN(Row(A1:A1000)^1, 1000), COUNTA(FILTER(A2:A32, B2:B32=D2)), 1), 1). The ‘n’ in the formula can be replaced to control the number of random dates you want to extract.
Option 2: Using RANDARRAY and FILTER
Alternatively, you can use the RANDARRAY function to pick random values based on a condition. Here’s how:
- Follow the steps mentioned in Option 1 to filter the dates based on the weekday/weekend criteria.
- Instead of using RANDBETWEEN, we’ll use the RANDARRAY function to generate random numbers. The formula would be:
=RANDARRAY(COUNTA(FILTER(A1:A,B1:B=D2)),1). - Finally, sort the filtered dates using the random numbers. The formula would be:
=SORTN(FILTER(A1:A,B1:B=D2),COUNTA(FILTER(A1:A,B1:B=D2)),0,RANDARRAY(COUNTA(FILTER(A1:A,B1:B=D2)),1),TRUE).
And voila! You’ve successfully picked random values based on a condition in Google Sheets.
Conclusion
Now that you know how to pick random values based on a condition in Google Sheets, you can apply the same techniques to solve similar problems. Play around with different criteria and formulas to customize your results.
If you want to explore more Google Sheets tips and tricks, check out Crawlan.com, your go-to resource for all things Google Sheets. Happy sheeting!
