by anthonysmoak
In this video I help you solve the dual problems of selecting a random value from an Excel list and selecting a number of random rows from a range of data in Excel. At times when I am generating a data-set to use in my video tutorials, I want to select a random selection of rows. Typically, because my data values are clumped together and are too similar to the data on preceding and subsequent rows.
SELECTING A RANDOM VALUE FROM A LIST
Enter three Excel formulas to save the day for selecting a random value from a list:
- ROWS()
- Returns the number of rows in a reference or array.
- RANDBETWEEN()
- Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
- INDEX()
- The INDEX function returns a value or the reference to a value from within a table or range.
- In the screenshot above notice that the ROWS() function returns the value of 20, which corresponds to the number of names listed in the cell range of A6 to A25.
- The RANDBETWEEN() function generated a random number between 1 and the value returned from ROWS() (i.e., 20). In this case, RANDBETWEEN() combined with ROWS() returned a value of 3.
- By combining the results from the first 2 functions, the INDEX() function searches our list and returns the value of the 3rd cell in the list (i.e., Flor McCard) because the RANDBETWEEN() function returned a value of 3.
When we put it all together it looks like the following:
=INDEX($A$6:$A$25,RANDBETWEEN(1,ROWS($A$6:$A$25)))
I choose to use the absolute cell reference notation with dollar signs although in this case it is not necessary since we are not copying our results to other cells.
SELECTING RANDOM ROWS FROM A LIST
We’ll only use 1 Excel formula to save the day for selecting random rows from a range:
- RAND()
- RAND() returns an evenly distributed random real number greater than or equal to 0 and less than 1. A new random real number is returned every time the worksheet is calculated.
By placing the RAND() function in a column co-located with your data, you will assign a random number to each row in your data-set or range.
Once that is done, all you have to do is sort your data by the RAND() column and then select however many rows you need. It’s that simple!
If you are like me, you probably need to see it in action to get a better understanding. Check out the video above and if you learned something, please go ahead and like it on my Youtube channel!
Thanks for your support!
All views and opinions are solely my own and do NOT necessarily reflect those of my employer.
References:
All Excel function definitions are from https://support.office.com
