Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
Hi,
I have looked at the Random Number Generator Add-in and the =RAND function but can't get either to do what I want (although I'm sure they can) I have a range of cells in a single column (A2:A3667), and I want a list of random numbers to fill that range using the values 1:3666, using each value once and once only. Effectively randomising the row numbers. How can I do this? Cheers, Matt |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
Matt,
In A2, enter =RAND() In B2, enter =RANK(A2,$A$2:$A$3667) then copy A2:B2 down to row 3667. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Matt D Francis" wrote in message ... Hi, I have looked at the Random Number Generator Add-in and the =RAND function but can't get either to do what I want (although I'm sure they can) I have a range of cells in a single column (A2:A3667), and I want a list of random numbers to fill that range using the values 1:3666, using each value once and once only. Effectively randomising the row numbers. How can I do this? Cheers, Matt |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
Hi,
Well that worked - wasn't quite the way I was expecting to be able to do it, but thanks! Can this guarantee that the numbers in column A will be unique? Also, don't know if this is normal but the random numbers I generate in Col A are all decimals, why's that? "Bob Phillips" wrote: Matt, In A2, enter =RAND() In B2, enter =RANK(A2,$A$2:$A$3667) then copy A2:B2 down to row 3667. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Matt D Francis" wrote in message ... Hi, I have looked at the Random Number Generator Add-in and the =RAND function but can't get either to do what I want (although I'm sure they can) I have a range of cells in a single column (A2:A3667), and I want a list of random numbers to fill that range using the values 1:3666, using each value once and once only. Effectively randomising the row numbers. How can I do this? Cheers, Matt |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
Because RAND() returns values from [0...1).
Another way, without the helper column: http://www.mcgimpsey.com/excel/udfs/randint.html In article , "Matt D Francis" wrote: Also, don't know if this is normal but the random numbers I generate in Col A are all decimals, why's that? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
I cannot guarantee that the numbers in A will be unique, but in all the
times I have used it I have never seen a duplicate. If you want to be sure, use A2: =RAND() B2: =RANK(A2,$A$2:$A$3667)+ROW()/10^7 C2: =RANK(B2,$B$2:$B$3667,1) and copy down -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Matt D Francis" wrote in message ... Hi, Well that worked - wasn't quite the way I was expecting to be able to do it, but thanks! Can this guarantee that the numbers in column A will be unique? Also, don't know if this is normal but the random numbers I generate in Col A are all decimals, why's that? "Bob Phillips" wrote: Matt, In A2, enter =RAND() In B2, enter =RANK(A2,$A$2:$A$3667) then copy A2:B2 down to row 3667. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Matt D Francis" wrote in message ... Hi, I have looked at the Random Number Generator Add-in and the =RAND function but can't get either to do what I want (although I'm sure they can) I have a range of cells in a single column (A2:A3667), and I want a list of random numbers to fill that range using the values 1:3666, using each value once and once only. Effectively randomising the row numbers. How can I do this? Cheers, Matt |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique RANDOM NUMBERS within specified range
"JE McGimpsey" wrote: Because RAND() returns values from [0...1). Another way, without the helper column: http://www.mcgimpsey.com/excel/udfs/randint.html In article , "Matt D Francis" wrote: Also, don't know if this is normal but the random numbers I generate in Col A are all decimals, why's that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Part Numbers In A Range | Excel Worksheet Functions | |||
Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
random numbers from a data of numbers? | Excel Worksheet Functions | |||
Random Assignment of Numbers | Excel Worksheet Functions | |||
Help on Random Numbers | Excel Discussion (Misc queries) |