» Random Selection from a List

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))

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))

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))

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!

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))

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