I would like to pick random values based on a given condition in Google Sheets. Which is the suitable function to do that?
There are three functions in Google Sheets that you can consider for this. They are RAND, RANDBETWEEN, and RANDARRAY. But none of them supports criteria as an argument in it.
In the above three functions, we can use the last two functions with other functions for our purpose. I will provide you the examples involving both the functions.
How to Pick Random Values with Condition in Google Docs Sheets
To pick random values based on the condition in Google Sheets you can use either of the above two functions with the function FILTER as a combo. Here are those examples.
Assume I have two column data (list). The first column contains all the dates in a month and the second column contains corresponding weekday/weekends.
I want to select a few dates randomly based on a specific weekday. See how to do that using the RANDBETWEEN as well as the RANDARRAY functions.
You may not find any logic in selecting dates as above. This is just for example. With the help of this tutorial, you will be able to use my combo formula in similar problems in Google Sheets.
You can see some interesting formula combinations below.
Sample Data:
Option 1: RANDBETWEEN + Filter to Include Criteria
Here I am going to extract random dates in which the day number falling on Sunday. I will show you how to randomly extract all the dates based on the above criteria and how to limit the days.
Here is the step by step instruction to code the formula.
See the criteria is in cell D2. First, use the below formula to filter the dates of which the day number falls on Sunday.
Formula # 1: Filter Dates Based on Weekday/Weekend
=filter(A2:A32,B2:B32=D2)As per the above sample data, the filter formula will return 4 dates. They are 05/05/19, 12/05/19, 19/05/19, and 26/05/19.
To randomly sort these dates, we must generate 4 random numbers. For that, we can use the RANDBETWEEN as below.
=ArrayFormula(Array_constrain(randbetween(Row(A1:A1000)^1,1000),4,1))Refer to my Google Sheets Functions Guide to learn/understand all the four functions used in the above formula.
This formula has an issue. It will always return 4 random numbers. But it may not match with the filter output as the filter may return more or fewer values. So replace the number 4 in the above formula with the below filter formula.
=counta(filter(A2:A32,B2:B32=D2))Then the dynamic RANDBETWEEN formula would look like as follows.
Formula # 2: Random Numbers Limited to the Number of Rows in the Filter Output
=ArrayFormula(Array_constrain(randbetween(Row(A1:A1000)^1,1000),counta(filter(A2:A32,B2:B32=D2)),1))Finally, sort the formula # 1 output with sort range as formula # 2. Here I am using SORTN not SORT as we can control the number of rows to return.
Generic Formula: Custom Sort Based on Random Numbers
=sortn(formula 1,n,0,formula 2)Here other than formula 1 and formula 2, you only need to change the ‘n’. Replace ‘n’ with 1 to get one random date, 2 to get two random dates, and 9^9 to get all randoms dates conditionally. You must not change the 0 in the generic formula as it’s tie mode in SORTN.
Final Formula:
=sortn(filter(A2:A32,B2:B32=D2),4,0,Array_constrain(randbetween(Row(A1:A1000)^1,1000),counta(filter(A2:A32,B2:B32=D2)),1),1)In this final formula, I have removed the ArrayFormula which was part of the formula 2. The reason is when combining formula 1 and formula 2 in the final formula, I have used SORTN. If you have SORTN, you can exclude the ArrayFormula.
Option 2: RANDARRAY + Filter to Include Criteria
You can use the RANDARRAY function too to pick random values based on condition in Google Sheets. It’s simpler than the above one.
Here also I am going to use the formula 1 and formula 2 inside SORTN. Here the formula 1 is the same above Filter formula. But the formula 2 is obviously different as we are using RANDARRAY here.
Here is that formula 2.
=randarray(COUNTA(FILTER(A1:A,B1:B=D2)),1)This formula would return 4 random numbers. It’s simpler than RANDBETWEEN as it’s by default an array formula.
Here also the final formula follows the same above generic formula pattern.
=SORTN(FILTER(A1:A,B1:B=D2),4,0,randarray(COUNTA(FILTER(A1:A,B1:B=D2)),1),TRUE)That’s all. Thanks for the stay. Enjoy!
Additional Resources:
