Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random 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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
Thanks, for takin the time. I will try that? Cheers!
"Chip Pearson" wrote: 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. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. "Max" wrote: 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
RagDyer, that was again well said, it seems quite simply, i guess if you know
your stuff. It liked it. Thank u all. "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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! "RayT" wrote: Thanks, for takin the time. I will try that? Cheers! "Chip Pearson" wrote: 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. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2008 Pearson Software Consulting, LLC www.cpearson.com (email on web site) "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
welcome, RayT
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RayT" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
Thanks for your feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayT" wrote in message ... RagDyer, that was again well said, it seems quite simply, i guess if you know your stuff. It liked it. Thank u all. "RagDyer" wrote: 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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayT" wrote in message ... Could you kindly assist me with a formula that generate 6 numbers between 1 and 49, without repeating any of the integers. Thanks! |
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
On Jan 14, 3:53*pm, "RagDyer" 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. |
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
RagDyer: Please explain Random number Generator
On Jan 14, 3:53*pm, "RagDyer" 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. |
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
On Jan 14, 9:09 pm, I wrote:
On Jan 14, 3:53 pm, "RagDyer" 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. [....] 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. 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. |
#14
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- wrote in message ... On Jan 14, 3:53 pm, "RagDyer" 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. |
#15
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Random number Generator
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random number generator | Excel Worksheet Functions | |||
Random Number generator | Excel Worksheet Functions | |||
Random Number Generator | Excel Worksheet Functions | |||
Random Number Generator | Excel Worksheet Functions | |||
Random number generator | Excel Worksheet Functions |