Non repeating random numbers

R

Rowland

I have a much larger range but for the this example I only use 10 cells.
I need to pull random numbers from a range without repeating any of them
INDEX(A$1:A$10,RAND()*10+1
This works but it obviously is prone to picking repeats.How can I do
this with a formula without repeating..I can't use a VBA solution in
this spreadsheet.I can put a big clunky If in there to check each one
but theres got to be a cleaner way.
 
G

Gary''s Student

Here the trick. Say you have numbers in A1 thru A100 and you want to sample
ten of them with any repeats. In B1 thru B100 enter the formula:

=RAND()

then sort cols A & B by B.

This will scramble the material in column A.

Just pick the first 10 items.
 
R

Rowland

Rowland said:
I have a much larger range but for the this example I only use 10 cells.
I need to pull random numbers from a range without repeating any of them
INDEX(A$1:A$10,RAND()*10+1
This works but it obviously is prone to picking repeats.How can I do
this with a formula without repeating..I can't use a VBA solution in
this spreadsheet.I can put a big clunky If in there to check each one
but theres got to be a cleaner way.


OK I think I have solved my problem with only adding one column
I add a column and enter Rand() down B1:B10(and then hide it)
In the C column where I need 10 non repeating random numbers from A1:A10
I use this formula
=INDIRECT("A"&RANK(B1,B$1:B$10))
It gets the Rank of the rand()number in B column(1 to 10) and uses that
with the indirect function to return a value from the A Column
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top