|  

» Random Selection from a List

Problem:

Randomly selecting one of the values from the list in column A.

Solution:

Use the INDEX, ROUND, RAND, and COUNTA functions in the following formula:
=INDEX(A2:A7,ROUND(RAND()*COUNTA(A2:A7),0))

Note:
The value returned by the formula will change with each recalculation ( key).
Screenshot // Random Selection from a List
Random Selection from a List


Rate This Tip
12 34 5
Rating: 3.56     Views: 33076
Random Selection Of Values
magarab
The idea is great, but the actual formula could generate #VALUE! cells, so rather propose to use the following formula instead:

=INDEX(A2:A7,ROUND(RAND()*(COUNTA(A2:A7)-1)+1,0))
Random Selection from List : Formula is not correct
Hollins Crompton
The formula and suggested correction (magarab) are both erroneous. Great idea but wrong. The problem lies with the rounding of the random generator. To simplify understanding, if there was a selection of only 3 (say A,B,C) then the rounded randomiser would produce the results in the following proportions, 3:2:1, i.e. half of the time it would produce A, a third of the time B and a sixth of the the time C.
The correction would be to use the ROUNDUP formula so it shows like this:

=INDEX(A2:A7,ROUNDUP(RAND()*COUNTA(A2:A7),0))

Random Selection from List : Formula is not correct
Hollins Crompton
The formula and suggested correction (magarab) are both erroneous. Great idea but wrong. The problem lies with the rounding of the random generator. To simplify understanding, if there was a selection of only 3 (say A,B,C) then the rounded randomiser would produce the results in the following proportions, 3:2:1, i.e. half of the time it would produce A, a third of the time B and a sixth of the the time C.
The correction would be to use the ROUNDUP formula so it shows like this:

=INDEX(A2:A7,ROUNDUP(RAND()*COUNTA(A2:A7),0))

duplicates
tx12345
When i fill down
=INDEX(A2:A7,ROUNDUP(RAND()*COUNTA(A2:A7),0))

I get several duplicates from the draw list. What if one wanted each random number to be unique that is produced by

=INDEX(A2:A7,ROUNDUP(RAND()*COUNTA(A2:A7),0))
??

TIA!
duplicates
bob
have you guys found a way pick random names from a list without getting duplicates.

I used this formula =INDEX(A2:A7,ROUNDUP(RAND()*COUNTA(A2:A7),0))
Selecting a random list (say random 5 samples) form a population size
Arpit Jain
the formula is great but for selecting more than one sample, the formula needs to be repeated for the required no. of times ,
hav you guys found out a way of selecting a random list of more than one samples ?

Arpit
Click here to post comment
For Registered Users
Name
Comment Title
Comments