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
|
|||
|
|||
|
#8
|
|||
|
|||
Harlan Grove wrote:
Undue skepticism about documented functionality isn't wisdom, it's paranoia. In my case, it is based on decades of experience with being on the oppposite side -- the person responsible for implementing and supporting some functionality. I know the value of the flexibility of undocumented behavior -- the ability to evolve behavior judiciously. I also know the "cop-out" value of undocumented behavior -- the freedom not to support such behavior when it is unwise to do so. And I know the danger of documenting "too well" -- the inflexibility that can cause because people depend on the documented behavior. Whether paranoia is warranted when using Microsoft software with Microsoft documentation is debatable. My comments have nothing to do with the endless parochial debates that some people like to engage in. In fact, my comments were honed by experience with software in another industry. Given the need for simulating sampling without replacement, would there ever be hardware RNGs without a library routine to produce samples without replacement? Sure! The hardware RNG I am familiar with does not. Why should it? Why would you expect it of a hardware RNG, if we don't see it with most software RNGs -- Excel, for example? ;-) It is trivial to implement "sampling without replacement" in almost any programming language, e.g. VBA. So there really is no need to provide the feature with any RNG implementation, be it hardware or software. |
#9
|
|||
|
|||
|
#11
|
|||
|
|||
Harlan Grove wrote:
OK, so are you assuming you're the only participant in these newsgroups with software development experience? This discussion is suffering from diminishing returns. But just to clarify: I never put myself above anyone else, nor did I disparage anyone. You questioned my judgment; I explained the basis for my judgment. That was all. |
#12
|
|||
|
|||
|
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 |