Unique random numbers from list
 

I would like to create a function that would put 5 random numbers from
a list. The list is in cells A5:A105 and the five random selections
can go in B5:B9. I'm using the formula:

=INDEX(A:A,RAND()*COUNTA(A:A))

This formula does work most of the time but does not guarantee that
the five random selections are unique. Any ideas on how this can be
done?

Unique random numbers from list
 

You're saying that you have 101 numbers in your list.

In an out of the way location on you sheet, say Z1 to Z101, enter the Rand
function.
In Z1 enter,
=Rand()
And copy down to Z101.

Then in B5, enter this formula:

=INDEX(A$5:A$105,RANK(Z1,Z$1:Z$101))

And copy down to B9.

This will give you a random selection from your list, "without replacement",
meaning ... no duplicates.

You will get a new random selection with every hit of <F9.

You could make your calc mode "manual", so that you don't inadvertently
refresh you random selection.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Matt" wrote in message
...
I would like to create a function that would put 5 random numbers from
a list. The list is in cells A5:A105 and the five random selections
can go in B5:B9. I'm using the formula:

=INDEX(A:A,RAND()*COUNTA(A:A))

This formula does work most of the time but does not guarantee that
the five random selections are unique. Any ideas on how this can be
done?

Unique random numbers from list
 

Are the numbers in A5:A105 unique -i.e. no duplicates?

Tyro

"Matt" wrote in message
...
I would like to create a function that would put 5 random numbers from
a list. The list is in cells A5:A105 and the five random selections
can go in B5:B9. I'm using the formula:

=INDEX(A:A,RAND()*COUNTA(A:A))

This formula does work most of the time but does not guarantee that
the five random selections are unique. Any ideas on how this can be
done?

Unique random numbers from list
 

That is correct. There are no duplicates in the original list.