Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique random numbers from list
That is correct. There are no duplicates in the original list.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unique RANDOM NUMBERS within specified range | Excel Worksheet Functions | |||
how to identify unique list of 200 random entries from a list of 3 | Excel Worksheet Functions | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) | |||
generate unique random numbers | New Users to Excel | |||
Random list (1-45) without repeating numbers? | Excel Worksheet Functions |