 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Random number Generator
Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! I don't believe there is anyway to do this with a formula; you'll need some VBA code. See http://www.cpearson.com/Excel/randomNumbers.aspx for quite a few choices. Look at the section entitled "Getting An Array Of Unique, Non-Duplicated Value" and the UniqueRandomLongs function.  SignatureCordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) > Could you kindly assist me with a formula that generate 6 numbers between > 1 > and 49, without repeating any of the integers. > Thanks! Thanks, for takin the time. I will try that? Cheers! > I don't believe there is anyway to do this with a formula; you'll need some > VBA code. See http://www.cpearson.com/Excel/randomNumbers.aspx for quite a [quoted text clipped - 5 lines] > > and 49, without repeating any of the integers. > > Thanks! One other play using formulas which might interest you .. Illustrated in this "Ready-to-randomize" sample: http://www.freefilehosting.net/download/3acjd Randomize 1 - 49 into a 6 col x 8 row grid.xls The set-up: Numbers 1 - 49 to be randomized listed in A1:A49 In B1: =RAND() In C1: =INDEX($A:$A,RANK(B1,B$1:B$49)) Copy B1:C1 down to C49 Place in say, E2: =INDEX($C:$C,ROWS($1:1)*6-6+COLUMNS($A:A)) Copy E2 across to J2, fill down to J10. Clear F10:J10. The grid E2:J9 returns 8 sets of 6 random numbers from the source in col A. E10 returns the last element. Press F9 to regenerate. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Hey Max thats some good stuff there man. Its almost the same as how RagDyer would do, well explained, u make it sound easy. Thanks mate. > One other play using formulas which might interest you .. > [quoted text clipped - 15 lines] > > Press F9 to regenerate. welcome, RayT SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Hey Max thats some good stuff there man. Its almost the same as how > RagDyer > would do, well explained, u make it sound easy. Thanks mate. Sorry, just detected that the earlier sample contained an inadvertent error* Here's the corrected version: http://www.freefilehosting.net/download/3adkm Randomize 1 - 49 into a 6 col x 8 row grid.xls *The formulas in the output grid E2:J10 got messed up. To correct, just re-copy E2 across/down to J10. Clear F10:J10. The grid E2:J9 will return 8 sets of 6 random numbers from the source in col A. E10 returns the last element. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- Using VBA is very interesting, i really would love to learn alot about that. Enjoyed working through the steps in the example from the page you mentioned. I sure would recommend anyone who loves to learn more to try it. I personally enjoyed it. Guez there are many ways to skin a cat. Cheers! > Thanks, for takin the time. I will try that? Cheers! > [quoted text clipped - 7 lines] > > > and 49, without repeating any of the integers. > > > Thanks! This should work for you: In an out-of-the-way location, say Column Z, enter the Rand function, In Z1 enter =Rand() And copy down to Z49. Then enter this formula wherever you wish: =INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49)) Copy down as many rows as you need random numbers. Each time you hit <F9>, you'll get a new random selection. You might choose to place your calc mode into Manual, so that you don't inadvertently refresh the list. SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Could you kindly assist me with a formula that generate 6 numbers between > 1 > and 49, without repeating any of the integers. > Thanks! RagDyer, that was again well said, it seems quite simply, i guess if you know your stuff. It liked it. Thank u all. > This should work for you: > [quoted text clipped - 17 lines] > > and 49, without repeating any of the integers. > > Thanks! Thanks for your feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > RagDyer, that was again well said, it seems quite simply, i guess if you > know [quoted text clipped - 22 lines] >> > and 49, without repeating any of the integers. >> > Thanks! > In an out-of-the-way location, say Column Z, enter the Rand > function[.] In Z1 enter =Rand()[.] And copy down to Z49. > > Then enter this formula wherever you wish: > =INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49)) > Copy down as many rows as you need random numbers. Can you please explain the theory of operation. I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the full INDEX expression returns. It seems that ROW($A$1:$A$49) is always 1. In any case, I agree with RayT that this is a clever, yet nicely simple solution to the problem. In this particular case, you're right, the Rand() function would suffice. However, I always suggest the combination with Index() as a more or less *generic* solution, which can be understood and built on. What if the OP *later* wanted numbers *other* then 1 to 49, say 10 to 58, or 100 to 148? =INDEX(ROW($A$100:$A$148),RANK(Z1,$Z$1:$Z$49)) OR, say that the list of numbers are *not consecutive*, OR, say that a random list of names is desired ... with the master list located at say J50 to J98: =INDEX($J$50:$J$98,RANK(Z1,$Z$1:$Z$49)) So, you can see how easily a revision might be accomplished if the necessary basic functions are presented at the outset. SignatureRegards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- On Jan 14, 3:53 pm, "RagDyer" <ragd...@cutoutmsn.com> wrote: > In an out-of-the-way location, say Column Z, enter the Rand > function[.] In Z1 enter =Rand()[.] And copy down to Z49. > > Then enter this formula wherever you wish: > =INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49)) > Copy down as many rows as you need random numbers. Can you please explain the theory of operation. I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the full INDEX expression returns. It seems that ROW($A$1:$A$49) is always 1. In any case, I agree with RayT that this is a clever, yet nicely simple solution to the problem. > In an out-of-the-way location, say Column Z, enter the Rand function[.] > In Z1 enter =Rand()[.] And copy down to Z49. > > Then enter this formula wherever you wish: > =INDEX(ROW($A$1:$A$49),RANK(Z1,$Z$1:$Z$49)) > Copy down as many rows as you need random numbers. Can you please explain the theory of operation. I agree that it works. But it seems that =RANK(Z1,$Z$1:$Z$49) would suffice. RANK(Z1,$Z$1:$Z$49) seems to return the same thing that the full INDEX expression returns. It seems that ROW($A$1:$A$49) is always 1. In any case, I agree with RayT that this is a clever, yet nicely simple solution to the problem. On Jan 14, 9:09 pm, I wrote: > > In an out-of-the-way location, say Column Z, enter the Rand function[.] > > In Z1 enter =Rand()[.] And copy down to Z49. [quoted text clipped - 6 lines] > full INDEX expression returns. It seems that ROW($A$1:$A$49) is > always 1. Only out of context. ROW($A$1:$A$49) returns the array {1,2,...,49}. In the context of INDEX(), the RANK() result (1,2,...,49) is used to index into that array. In this context, I believe that using INDEX() and ROW() is redundant, since the OP is interested in randomly choosing amount 1,2,...,49, which is exactly what RANK() returns, given that Z1:Z49 contains random values. However, if the OP had been interested in, for example, randomly selecting from the range 13-61, then perhaps INDEX(ROW($A$13:$A $61),RANK(Z1,$Z$1:$Z$49)) could be used -- although, I believe that 12+RANK(Z1,$Z$1:$Z$49) would suffice. On the other hand, if the OP had wanted to randomly select unique values from A1:A49, then I believe INDEX($A$1:$A$49,RANK(Z1,$Z$1:$Z $49)) could be used, copying down for as many selections as required. RagDyer (or any other expert), please comment. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.