Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Random numbers
I want to generate numbers (1 to 15) in cells A1 through O1, but the number in each cells should be unique compare to the other cells, how can I do it? If I use =randbetween(1,15), I can not get unique number in each cell, some numbers are duplicated. -- john liem |
#2
|
|||
|
|||
John, The problem is that you want random, and unique which means not random. You can achieve your results by putting in cells A1 to O1 =int(rand()*99999999) in cells A2 to O2 the numbers 1 to 15 in cells A3 to O3 =HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE) This should give you a random shuffle each time the worksheet calculates (press F9) john liem Wrote: I want to generate numbers (1 to 15) in cells A1 through O1, but the number in each cells should be unique compare to the other cells, how can I do it? If I use =randbetween(1,15), I can not get unique number in each cell, some numbers are duplicated. -- john liem -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=474360 |
#3
|
|||
|
|||
Bryan Hessey wrote:
The problem is that you want random, and unique which means not random. That is incorrect. The OP simply wants random selection "without replacement". Very common requirement. Nothing less random about it. You can achieve your results by putting in cells A1 to O1 =int(rand()*99999999) .... Or simply =RAND(). in cells A2 to O2 the numbers 1 to 15 in cells A3 to O3 =HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE) That is a nice idea, but I don't believe it is guaranteed to give unique values. Consider the rare case when RAND() gives the same result twice. I believe HLOOKUP() will return the same value from $A2:O2. The probability of that is increased with Bryan's INT(RAND()*N) approach. Normally I manually sort $A1:$O2 (in Bryan's example) based on column A. If there are duplicates in $A1:$O1, the order of corresponding unique values in $A2:$O2 is "arbitrary". (But not unpredictable. It depends on the sort algorithm). I would like to avoid the manual sort myself. Building on Bryan's idea, if we could determine the column that the SMALL() value came from, we could build a reference to $A2:$O2 using OFFSET() or similar functions. Offhand, I do not know how to do that. Hopefully, someone else can offer a solution. I believe that other threads on the same subject point to one or another MVP's web site with solutions. Search for "random" in the Excel newsgroups/forums. ----- john liem Wrote: I want to generate numbers (1 to 15) in cells A1 through O1, but the number in each cells should be unique compare to the other cells, how can I do it? If I use =randbetween(1,15), I can not get unique number in each cell, some numbers are duplicated. |
#4
|
|||
|
|||
|
#5
|
|||
|
|||
wrote...
Bryan Hessey wrote: .... You can achieve your results by putting in cells A1 to O1 =int(rand()*99999999) ... Or simply =RAND(). Stronger statement in order. There's a small chance of duplication of integers using INT(RAND()*99999999). There's *NO* chance of duplication using RAND() alone when only 15 numbers are involved. NEVER round pseudorandom numbers unless you need the rounded results. If they're only used in intermediate calculations, *ALWAYS* leave them as-is (unrounded). in cells A2 to O2 the numbers 1 to 15 in cells A3 to O3 =HLOOKUP(SMALL($A1:$O1,COLUMN()),$A1:$O2,2,FALSE ) That is a nice idea, but I don't believe it is guaranteed to give unique values. Consider the rare case when RAND() gives the same result twice. . . . If the OP uses your simple =RAND() in row 1, this isn't an issue. The only way you get duplicates from pseudorandom number generators is by drawing more deviates than the period of the generator. The period of Excel's generator is more than 1,000,000, so no duplicates in a range of only 15 cells. . . . I believe HLOOKUP() will return the same value from $A2:O2. The probability of that is increased with Bryan's INT(RAND()*N) approach. It's possible in Bryan's approach *ONLY* when using his truncated integer random deviates. Normally I manually sort $A1:$O2 (in Bryan's example) based on column A. If there are duplicates in $A1:$O1, the order of corresponding unique values in $A2:$O2 is "arbitrary". (But not unpredictable. It depends on the sort algorithm). I would like to avoid the manual sort myself. Building on Bryan's idea, if we could determine the column that the SMALL() value came from, we could build a reference to $A2:$O2 using OFFSET() or similar functions. Offhand, I do not know how to do that. Hopefully, someone else can offer a solution. .... If you have a single column, N row range D of distinct values, and you want to draw a sample of size K <= N from D without replacement, then all you need is another single column, N row range, RV, of distinct random values (=RAND() sufficient for this) and formulas like K1: =INDEX(D,COUNTIF(RV,"<="&INDEX(RV,ROWS(K$1:K1)))) K1 filled down as far as needed. |
#6
|
|||
|
|||
Harlan Grove wrote:
There's *NO* chance of duplication using RAND() alone when only 15 numbers are involved. [....] The only way you get duplicates from pseudorandom number generators is by drawing more deviates than the period of the generator. The period of Excel's generator is more than 1,000,000, so no duplicates in a range of only 15 cells. Good point. I thought of that, too. But I try to not make ass-u-me-tions about algorithms that I have no control over, and especially about undocumented features. For example, what if RAND() later can utilize a hardware RNG, if it is installed? Don't get wrong: I must admit that your assertion is quite likely right in the case of generating only 15 RAND() values. I was probably being a little anal. But I thought the point was important to make since some people will apply these ideas to very different circumstances. |
#7
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Generation of random numbers and sum of those with a condition | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) | |||
random numbers from a data of numbers? | Excel Worksheet Functions |