Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Random Numbers
Hello from Steved
ok I am no thinking straight What do I need to do please to have a number in a row 1 to 40 as an example below 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once six rows deep yes the other rows can have the same numbers as above or below =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60)) =RAND() copied H1:H60 |
#2
|
|||
|
|||
If you need to randomly select 6 numbers from the range 1-40 with no repeats:
1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column This will jumble the numbers 1-40 randomly. Just pick the first six numbers. -- Gary''s Student "Steved" wrote: Hello from Steved ok I am no thinking straight What do I need to do please to have a number in a row 1 to 40 as an example below 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once six rows deep yes the other rows can have the same numbers as above or below =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60)) =RAND() copied H1:H60 |
#3
|
|||
|
|||
Hello Gary From Steved
Can your formula devoloped to display 6 columns across and 10 rows deep please. Thankyou. "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column This will jumble the numbers 1-40 randomly. Just pick the first six numbers. -- Gary''s Student "Steved" wrote: Hello from Steved ok I am no thinking straight What do I need to do please to have a number in a row 1 to 40 as an example below 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once six rows deep yes the other rows can have the same numbers as above or below =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60)) =RAND() copied H1:H60 |
#4
|
|||
|
|||
Steved wrote...
.... Can your formula devoloped to display 6 columns across and 10 rows deep please. .... "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column .... You don't need steps 1 or 3. All you need is a range of 60 cells (10*6) all containing =RAND(). If that range were named PRNA and the top-left cell of your 10 by 6 result range were C5, enter the following formula in C5. C5: =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Select C5 and fill right into D5:H5. Then select C5:H5 and fill down into C6:H14. |
#5
|
|||
|
|||
Yes.
In my method we are selectimg 6 random numbers in a column. Select all six numbers and push copy. Go to the place in the first row and paste/special with the transpose mark checked. This will paste the column into your first row. Now we need to fill rows 2-10 and we will do the same thing 1. re-calculate the random numbers (CNTRL-ALT-F9) 2. re-sort the columns to get fresh random digits 3. copy/paste into the next row -- Gary''s Student "Steved" wrote: Hello Gary From Steved Can your formula devoloped to display 6 columns across and 10 rows deep please. Thankyou. "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column This will jumble the numbers 1-40 randomly. Just pick the first six numbers. -- Gary''s Student "Steved" wrote: Hello from Steved ok I am no thinking straight What do I need to do please to have a number in a row 1 to 40 as an example below 1,23,9,25,33,40 each row needs to be 1 to 40 but number no more than once six rows deep yes the other rows can have the same numbers as above or below =INDEX(ROW($A$1:$F$10),RANK($H1,$H$1:$H$60)) =RAND() copied H1:H60 |
#6
|
|||
|
|||
Hello Harlan from Steve
I think i'm missing something here I followed what you put below naming the range PNRA ok =RAND() are in cell A1:F10 I highlighted and then I Insert Name Define please I am lost where do I put the below COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Also do I change your $C$5:$C$5 to $A$1:$A$1 ok also please what is require to reconize the number between 1 to 40. Thankyou. "Harlan Grove" wrote: Steved wrote... .... Can your formula devoloped to display 6 columns across and 10 rows deep please. .... "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column .... You don't need steps 1 or 3. All you need is a range of 60 cells (10*6) all containing =RAND(). If that range were named PRNA and the top-left cell of your 10 by 6 result range were C5, enter the following formula in C5. C5: =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Select C5 and fill right into D5:H5. Then select C5:H5 and fill down into C6:H14. |
#7
|
|||
|
|||
You can try this:
Enter =RAND() In AA1 and drag across to BN1, Then down to BN10, So you have a 40 column by 10 row array of random numbers. Then, enter this formula anywhere you wish: =INDEX(ROW($A1:$A40),RANK(AA1,$AA1:$BN1)) Now, copy this formula across 6 columns. Then, copy down 10 rows. This should give you what you're looking for. You'll get a new set of numbers with each hit of <F9. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steved" wrote in message ... Hello Harlan from Steve I think i'm missing something here I followed what you put below naming the range PNRA ok =RAND() are in cell A1:F10 I highlighted and then I Insert Name Define please I am lost where do I put the below COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Also do I change your $C$5:$C$5 to $A$1:$A$1 ok also please what is require to reconize the number between 1 to 40. Thankyou. "Harlan Grove" wrote: Steved wrote... .... Can your formula devoloped to display 6 columns across and 10 rows deep please. .... "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column .... You don't need steps 1 or 3. All you need is a range of 60 cells (10*6) all containing =RAND(). If that range were named PRNA and the top-left cell of your 10 by 6 result range were C5, enter the following formula in C5. C5: =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Select C5 and fill right into D5:H5. Then select C5:H5 and fill down into C6:H14. |
#8
|
|||
|
|||
"Steved" wrote...
.... I followed what you put below naming the range PNRA ok =RAND() are in cell A1:F10 I highlighted and then I Insert Name Define please I am lost where do I put the below COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) First, you're missing the initial equal sign. I may have misunderstood your question. Looks like you want 10 samples of 6 numbers each of which is drawn without replacement from 1-40. If so, then you still don't need anything more than a 40 cell range each cell in which containing =RAND(), which I'll still call PRNA. I'll further assume that PRNA is 40 rows in a single column. Then, in another range WHICH YOU MUST CHOOSE (but for my convenience, I'll continue to use C5:H14, so in my case, the cells containing the =RAND() formula don't overlar C5:H14), select C5:H5 and enter the following array formula. C5:H5 [array formula]: =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1))) Select C5:H5 and fill down into C6:H14. |
#9
|
|||
|
|||
Hello Ragdyer from Steved
Excellent thankyou. "Ragdyer" wrote: You can try this: Enter =RAND() In AA1 and drag across to BN1, Then down to BN10, So you have a 40 column by 10 row array of random numbers. Then, enter this formula anywhere you wish: =INDEX(ROW($A1:$A40),RANK(AA1,$AA1:$BN1)) Now, copy this formula across 6 columns. Then, copy down 10 rows. This should give you what you're looking for. You'll get a new set of numbers with each hit of <F9. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steved" wrote in message ... Hello Harlan from Steve I think i'm missing something here I followed what you put below naming the range PNRA ok =RAND() are in cell A1:F10 I highlighted and then I Insert Name Define please I am lost where do I put the below COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Also do I change your $C$5:$C$5 to $A$1:$A$1 ok also please what is require to reconize the number between 1 to 40. Thankyou. "Harlan Grove" wrote: Steved wrote... .... Can your formula devoloped to display 6 columns across and 10 rows deep please. .... "Gary''s Student" wrote: If you need to randomly select 6 numbers from the range 1-40 with no repeats: 1. Enter the numbers 1-40 into an un-used column 2. Enter =RAND() into the cells of the adjacent column 3. Sort the two columns by the RAND column .... You don't need steps 1 or 3. All you need is a range of 60 cells (10*6) all containing =RAND(). If that range were named PRNA and the top-left cell of your 10 by 6 result range were C5, enter the following formula in C5. C5: =COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) Select C5 and fill right into D5:H5. Then select C5:H5 and fill down into C6:H14. |
#10
|
|||
|
|||
Hello Harlan from Steved
ok put =RAND() in A1:A40 Then Insert, Name, Define, and typed PRNA then add and ok I then C5:H5 [array formula]: using Ctrl Shift Enter =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1))) Select C5:H5 and fill down into C6:H14. Done all off the above pushed F9 and on the same row I get the same number twice. Am I missing something here. Cheers "Harlan Grove" wrote: "Steved" wrote... .... I followed what you put below naming the range PNRA ok =RAND() are in cell A1:F10 I highlighted and then I Insert Name Define please I am lost where do I put the below COUNTIF(PRNA,"<="&INDEX(PRNA,ROWS($C$5:C5)*6-COLUMNS($C$5:C5)+1)) First, you're missing the initial equal sign. I may have misunderstood your question. Looks like you want 10 samples of 6 numbers each of which is drawn without replacement from 1-40. If so, then you still don't need anything more than a 40 cell range each cell in which containing =RAND(), which I'll still call PRNA. I'll further assume that PRNA is 40 rows in a single column. Then, in another range WHICH YOU MUST CHOOSE (but for my convenience, I'll continue to use C5:H14, so in my case, the cells containing the =RAND() formula don't overlar C5:H14), select C5:H5 and enter the following array formula. C5:H5 [array formula]: =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,6,1))) Select C5:H5 and fill down into C6:H14. |
#11
|
|||
|
|||
"Steved" wrote...
.... =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1))) .... Sorry, I screwed this up. Swap the 6 and 1 arguments, so =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6))) |
#12
|
|||
|
|||
Hello Harlan from Steved
Harlan done as discribed below but still have the issue offsame number twice omn the same line in this case row 8 i've 13 twice and row 10 I have 24 twice. Is it possible please to have this issue where their are no doubles. Thankyou for your patience. "Harlan Grove" wrote: "Steved" wrote... .... =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1))) .... Sorry, I screwed this up. Swap the 6 and 1 arguments, so =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6))) |
#13
|
|||
|
|||
Hello,
I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd |
#14
|
|||
|
|||
You're *not* looking for random numbers, since true randomness accepts the
possibility of duplication, since each item has an equal chance of occurring at each interval. You're looking for a random *order* of specific items (numbers 1 to 40). So, you therefore need, first of all, the specific items so that they can be rearranged randomly. That's the numbers of the rows, 1 to 40, making the construction of an actual list unnecessary. Next, the list is rearranged virtually, by accessing the 40 columns of random numbers and ranking these numbers, where this ranking is mirrored in the indexed row numbers. Even if these *true* random numbers are duplicated, and they are ranked *equally*, since they designate the row numbers, there *cannot* be a duplicated return, since there are *no* duplicate row numbers existing. And since each row must be independent of the other rows, so that duplication is avoided, you need a separate row of random numbers to be ranked differently from the others. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Steved" wrote in message ... Hello Harlan from Steved Harlan done as discribed below but still have the issue offsame number twice omn the same line in this case row 8 i've 13 twice and row 10 I have 24 twice. Is it possible please to have this issue where their are no doubles. Thankyou for your patience. "Harlan Grove" wrote: "Steved" wrote... .... =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1))) .... Sorry, I screwed this up. Swap the 6 and 1 arguments, so =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6))) |
#15
|
|||
|
|||
Hello Bernd from Steved
I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd |
#16
|
|||
|
|||
Hello RagDyeR from Steved
I am using your formula and so far I have'nt got any doubles. I've tried it several times yet to produce a double. So yes I'm Using it. Thankyou "RagDyeR" wrote: You're *not* looking for random numbers, since true randomness accepts the possibility of duplication, since each item has an equal chance of occurring at each interval. You're looking for a random *order* of specific items (numbers 1 to 40). So, you therefore need, first of all, the specific items so that they can be rearranged randomly. That's the numbers of the rows, 1 to 40, making the construction of an actual list unnecessary. Next, the list is rearranged virtually, by accessing the 40 columns of random numbers and ranking these numbers, where this ranking is mirrored in the indexed row numbers. Even if these *true* random numbers are duplicated, and they are ranked *equally*, since they designate the row numbers, there *cannot* be a duplicated return, since there are *no* duplicate row numbers existing. And since each row must be independent of the other rows, so that duplication is avoided, you need a separate row of random numbers to be ranked differently from the others. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Steved" wrote in message ... Hello Harlan from Steved Harlan done as discribed below but still have the issue offsame number twice omn the same line in this case row 8 i've 13 twice and row 10 I have 24 twice. Is it possible please to have this issue where their are no doubles. Thankyou for your patience. "Harlan Grove" wrote: "Steved" wrote... .... =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*R AND()),0,6,1))) .... Sorry, I screwed this up. Swap the 6 and 1 arguments, so =COUNTIF(PRNA,"<="&TRANSPOSE(OFFSET(PRNA,INT(35*RA ND()),0,1,6))) |
#17
|
|||
|
|||
Did you put it in a general module?
Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson |
#18
|
|||
|
|||
Yes
top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson |
#19
|
|||
|
|||
It sounds like you pasted it under the ThisWorkbook module.
Once you get to the VBE (alt-f11 is nice) hit ctrl-r (to see the project explorer) find your project rightclick on it Insert|Module Paste it there. (look under the ThisWorkbook module and delete it from there if I guessed right.) Then back to excel and recalculate. Steved wrote: Yes top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson -- Dave Peterson |
#20
|
|||
|
|||
Thanks very much Dave
Yes I carried out what you described and yes it created Random Numbers. I still have a Issue and that is when pushing F9 it will not recalculate. And to Bernd if you get to read this yes I get a double on the same line Line 1 No 7, Line 4 No 35, Line 6 No 12, Line 8 No 40, and line 10 12 As pasted below. 26 40 17 12 7 7 29 22 24 12 13 31 1 31 33 29 2 17 35 32 15 39 35 3 38 15 21 31 3 24 19 12 25 26 11 12 34 33 24 40 37 10 28 40 10 22 5 40 28 1 24 5 5 32 12 2 12 16 13 38 Thanks once again Dave. "Dave Peterson" wrote: It sounds like you pasted it under the ThisWorkbook module. Once you get to the VBE (alt-f11 is nice) hit ctrl-r (to see the project explorer) find your project rightclick on it Insert|Module Paste it there. (look under the ThisWorkbook module and delete it from there if I guessed right.) Then back to excel and recalculate. Steved wrote: Yes top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson -- Dave Peterson |
#21
|
|||
|
|||
Hellom from Steved
I had False change it to true and now recalculating. {=UniqRandInt(40, False)} now {=UniqRandInt(40, True)} Thankyou. "Steved" wrote: Thanks very much Dave Yes I carried out what you described and yes it created Random Numbers. I still have a Issue and that is when pushing F9 it will not recalculate. And to Bernd if you get to read this yes I get a double on the same line Line 1 No 7, Line 4 No 35, Line 6 No 12, Line 8 No 40, and line 10 12 As pasted below. 26 40 17 12 7 7 29 22 24 12 13 31 1 31 33 29 2 17 35 32 15 39 35 3 38 15 21 31 3 24 19 12 25 26 11 12 34 33 24 40 37 10 28 40 10 22 5 40 28 1 24 5 5 32 12 2 12 16 13 38 Thanks once again Dave. "Dave Peterson" wrote: It sounds like you pasted it under the ThisWorkbook module. Once you get to the VBE (alt-f11 is nice) hit ctrl-r (to see the project explorer) find your project rightclick on it Insert|Module Paste it there. (look under the ThisWorkbook module and delete it from there if I guessed right.) Then back to excel and recalculate. Steved wrote: Yes top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson -- Dave Peterson |
#22
|
|||
|
|||
Think you might also be interested in this program (full details inside):
http://www.savefile.com/files/7565212 File: Randomization_Lotto_program.xls It fits the bill here .. The core functionality is driven via the "RandLotto" UDF by Dave Hawley & JE McGimpsey Just enter the settings for the game in B6 to D6 in the sheet: Draw, for example in your case: From: 1 (in B6) To: 40 (in C6) Pick#: 6 (in D6) Pick# is the number of numbers in a set to be picked for the game. The settings entered will be used in the formula in B2: =randlotto(B6,C6,D6) Then just click the Draw button to generate as many random sets of unique numbers from within the range defined under "From" and "To" as desired. Generated sets of numbers will be frozen in col G from G2 down. One click of the Draw button generates one set. To reset / clear col G, just click the Reset button. The RandLotto UDF and the subs Draw and Reset for the 2 buttons described are listed in the "Notes" sheet The program's easy to use, and fun, too ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#23
|
|||
|
|||
I've never used =UniqRandInt() but I have used J.E. McGimpsey's version:
http://www.mcgimpsey.com/excel/udfs/randint.html You may want to take a look (just for curiosity's sake). Steved wrote: Hellom from Steved I had False change it to true and now recalculating. {=UniqRandInt(40, False)} now {=UniqRandInt(40, True)} Thankyou. "Steved" wrote: Thanks very much Dave Yes I carried out what you described and yes it created Random Numbers. I still have a Issue and that is when pushing F9 it will not recalculate. And to Bernd if you get to read this yes I get a double on the same line Line 1 No 7, Line 4 No 35, Line 6 No 12, Line 8 No 40, and line 10 12 As pasted below. 26 40 17 12 7 7 29 22 24 12 13 31 1 31 33 29 2 17 35 32 15 39 35 3 38 15 21 31 3 24 19 12 25 26 11 12 34 33 24 40 37 10 28 40 10 22 5 40 28 1 24 5 5 32 12 2 12 16 13 38 Thanks once again Dave. "Dave Peterson" wrote: It sounds like you pasted it under the ThisWorkbook module. Once you get to the VBE (alt-f11 is nice) hit ctrl-r (to see the project explorer) find your project rightclick on it Insert|Module Paste it there. (look under the ThisWorkbook module and delete it from there if I guessed right.) Then back to excel and recalculate. Steved wrote: Yes top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#24
|
|||
|
|||
Hi Dave and Steved,
I got the idea for my function from JE's older version. He adapted his algorithm in June similar to mine. What's left is that he could omit the check whether nCount =1. Then its just a question of personal taste whether one would like to call a function RandInt() or UniqRandint(), whether one would like to define the lower and upper border of the numbers or just the range and whether the "volatility" of the function should be a parameter. Steved, I hope you could solve your problem? Regards, Bernd |
#25
|
|||
|
|||
Thankyou Max.
"Max" wrote: Think you might also be interested in this program (full details inside): http://www.savefile.com/files/7565212 File: Randomization_Lotto_program.xls It fits the bill here .. The core functionality is driven via the "RandLotto" UDF by Dave Hawley & JE McGimpsey Just enter the settings for the game in B6 to D6 in the sheet: Draw, for example in your case: From: 1 (in B6) To: 40 (in C6) Pick#: 6 (in D6) Pick# is the number of numbers in a set to be picked for the game. The settings entered will be used in the formula in B2: =randlotto(B6,C6,D6) Then just click the Draw button to generate as many random sets of unique numbers from within the range defined under "From" and "To" as desired. Generated sets of numbers will be frozen in col G from G2 down. One click of the Draw button generates one set. To reset / clear col G, just click the Reset button. The RandLotto UDF and the subs Draw and Reset for the 2 buttons described are listed in the "Notes" sheet The program's easy to use, and fun, too ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#26
|
|||
|
|||
Thankyou Dave.
"Dave Peterson" wrote: I've never used =UniqRandInt() but I have used J.E. McGimpsey's version: http://www.mcgimpsey.com/excel/udfs/randint.html You may want to take a look (just for curiosity's sake). Steved wrote: Hellom from Steved I had False change it to true and now recalculating. {=UniqRandInt(40, False)} now {=UniqRandInt(40, True)} Thankyou. "Steved" wrote: Thanks very much Dave Yes I carried out what you described and yes it created Random Numbers. I still have a Issue and that is when pushing F9 it will not recalculate. And to Bernd if you get to read this yes I get a double on the same line Line 1 No 7, Line 4 No 35, Line 6 No 12, Line 8 No 40, and line 10 12 As pasted below. 26 40 17 12 7 7 29 22 24 12 13 31 1 31 33 29 2 17 35 32 15 39 35 3 38 15 21 31 3 24 19 12 25 26 11 12 34 33 24 40 37 10 28 40 10 22 5 40 28 1 24 5 5 32 12 2 12 16 13 38 Thanks once again Dave. "Dave Peterson" wrote: It sounds like you pasted it under the ThisWorkbook module. Once you get to the VBE (alt-f11 is nice) hit ctrl-r (to see the project explorer) find your project rightclick on it Insert|Module Paste it there. (look under the ThisWorkbook module and delete it from there if I guessed right.) Then back to excel and recalculate. Steved wrote: Yes top left corner right clicked on excel icon opened it and pasted it. "Dave Peterson" wrote: Did you put it in a general module? Steved wrote: Hello Bernd from Steved I've put your UDF in VBA and Copied =UniqRandInt(40, false) ok it has #NAME? What have I not done right please I presume F9 to execute Thankyou. "Bernd Plumhoff" wrote: Hello, I suggest to insert a VBA module with my UDF function UniqRandInt() (see www.sulprobil.com), then select cells A1:F1 enter =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. I admire elegant worksheet function solutions, but if you like to have a general and robust solution, a thoroughly tested UDF function might be the answer of your choice (don't trust my function, test it!). HTH, Bernd -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#27
|
|||
|
|||
Hello Bernd from Steved
I like the i'dea Bernd off your development to VBA I found this to be quite interesting, I conceed that I'm not a expert in this field however I will overtime work out a solution using your program, it is just a matter off I dentifying each row and putting in an argument to exclude any doubles. Once again Thankyou. "Bernd Plumhoff" wrote: Hi Dave and Steved, I got the idea for my function from JE's older version. He adapted his algorithm in June similar to mine. What's left is that he could omit the check whether nCount =1. Then its just a question of personal taste whether one would like to call a function RandInt() or UniqRandint(), whether one would like to define the lower and upper border of the numbers or just the range and whether the "volatility" of the function should be a parameter. Steved, I hope you could solve your problem? Regards, Bernd |
#28
|
|||
|
|||
"Steved" wrote:
... I get a double on the same line .. I will overtime work out a solution using your program, If I'm not mistaken, you got duplicates on the same line for some lines as you didn't enter Bernd's UDF as a multi-cell array, re-his advice in his original response below: "Bernd Plumhoff" wrote: then select cells A1:F1 (< this range selection is important) enter: =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. Probably you might have "wrongly" array-entered the UDF into a *single* cell, say A1, copied it across to F1, then filled A1:F1 down to F10. And that's why you got a few lines with duplicates within the same line. Try it again by selecting the range A1:F1 first, then paste into the *formula bar*: =UniqRandInt(40, FALSE) and array-enter with CSE The same formula (with curly braces inserted by Excel): {=UniqRandInt(40, FALSE)} will appear in every cell within A1:F1 but each cell will return a different number Then just select A1:F1 and fill down to say, F10, and there shouldn't be any more duplicates within the same line (for any one line) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#29
|
|||
|
|||
true randomness accepts the possibility of duplication
Just to be picky <g, what about the situation where you are selecting WITHOUT REPLACEMENT items at random from a group of unique items? The selection can be totally random here, and there will be no duplicates. |
#30
|
|||
|
|||
Hello Max from Steved
Yes I followed your instructions and yes their is no doubles Question Do I have to do this each time meaning why can I not push F9 to recalculate. At the moment when I push F9 nothing happens. Thankyou. "Max" wrote: "Steved" wrote: ... I get a double on the same line .. I will overtime work out a solution using your program, If I'm not mistaken, you got duplicates on the same line for some lines as you didn't enter Bernd's UDF as a multi-cell array, re-his advice in his original response below: "Bernd Plumhoff" wrote: then select cells A1:F1 (< this range selection is important) enter: =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. Probably you might have "wrongly" array-entered the UDF into a *single* cell, say A1, copied it across to F1, then filled A1:F1 down to F10. And that's why you got a few lines with duplicates within the same line. Try it again by selecting the range A1:F1 first, then paste into the *formula bar*: =UniqRandInt(40, FALSE) and array-enter with CSE The same formula (with curly braces inserted by Excel): {=UniqRandInt(40, FALSE)} will appear in every cell within A1:F1 but each cell will return a different number Then just select A1:F1 and fill down to say, F10, and there shouldn't be any more duplicates within the same line (for any one line) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#31
|
|||
|
|||
Hello from Steved
If I highlight the cells A1:F10 edit delete cells and then undo redo it recalculates interesting so what have I not done to allow this to recalculate please. Thankyou. "Steved" wrote: Hello Max from Steved Yes I followed your instructions and yes their is no doubles Question Do I have to do this each time meaning why can I not push F9 to recalculate. At the moment when I push F9 nothing happens. Thankyou. "Max" wrote: "Steved" wrote: ... I get a double on the same line .. I will overtime work out a solution using your program, If I'm not mistaken, you got duplicates on the same line for some lines as you didn't enter Bernd's UDF as a multi-cell array, re-his advice in his original response below: "Bernd Plumhoff" wrote: then select cells A1:F1 (< this range selection is important) enter: =UniqRandInt(40, false) as array formula (CTRL+SHIFT+ENTER) and copy this down to A10:F10. Probably you might have "wrongly" array-entered the UDF into a *single* cell, say A1, copied it across to F1, then filled A1:F1 down to F10. And that's why you got a few lines with duplicates within the same line. Try it again by selecting the range A1:F1 first, then paste into the *formula bar*: =UniqRandInt(40, FALSE) and array-enter with CSE The same formula (with curly braces inserted by Excel): {=UniqRandInt(40, FALSE)} will appear in every cell within A1:F1 but each cell will return a different number Then just select A1:F1 and fill down to say, F10, and there shouldn't be any more duplicates within the same line (for any one line) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#32
|
|||
|
|||
"Steved" wrote
.. so what have I not done to allow this to recalculate please. Nothing <g. Because Bernd's UDF is not volatile, as he had explained earlier. If you want it to recalc on pressing F9, try using JE's RandInt() UDF (see link provided by Dave P earlier) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#33
|
|||
|
|||
Thankyou Max Understood
I just was'nt thinking "Max" wrote: "Steved" wrote .. so what have I not done to allow this to recalculate please. Nothing <g. Because Bernd's UDF is not volatile, as he had explained earlier. If you want it to recalc on pressing F9, try using JE's RandInt() UDF (see link provided by Dave P earlier) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#34
|
|||
|
|||
"RagDyeR" wrote:
.. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. Think Harlan did accomplish this a few years back ? <g (and in a very concise set-up, too !) re his response at: http://tinyurl.com/b2oan Here's an adaptation of Harlan's solution to the OP's pick 6 out of 40 case Define* a name: LottoNumbers referring to: =ROW(INDIRECT("1:40")) *via Insert Name Define Then in A1 enter the formula: =INT(1+COUNT(LottoNumbers)*RAND()) In B1, enter the array formula: =LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumb ers), INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND())) Copy B1 to F1 Select A1:F1, fill down to say, F10 A1:F10 will return 10 sets of 6 numbers (from 1-40) w/o duplicates in any one set Press F9 to recalc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#35
|
|||
|
|||
You're welcome, Steved !
Btw, I've just posted a response to RagDyeR in the other branch It's an adaptation of a past, concise solution by Harlan to suit your situation. You might want to check it out as well .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#36
|
|||
|
|||
Do you realize that you have made it necessary for me to now go out and buy
a hat!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Max" wrote in message ... "RagDyeR" wrote: .. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. Think Harlan did accomplish this a few years back ? <g (and in a very concise set-up, too !) re his response at: http://tinyurl.com/b2oan Here's an adaptation of Harlan's solution to the OP's pick 6 out of 40 case Define* a name: LottoNumbers referring to: =ROW(INDIRECT("1:40")) *via Insert Name Define Then in A1 enter the formula: =INT(1+COUNT(LottoNumbers)*RAND()) In B1, enter the array formula: =LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumb ers), INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND())) Copy B1 to F1 Select A1:F1, fill down to say, F10 A1:F10 will return 10 sets of 6 numbers (from 1-40) w/o duplicates in any one set Press F9 to recalc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#37
|
|||
|
|||
Hello Max from Steved
Thanks for effort on my issue. Cheers. "Max" wrote: "RagDyeR" wrote: .. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. Think Harlan did accomplish this a few years back ? <g (and in a very concise set-up, too !) re his response at: http://tinyurl.com/b2oan Here's an adaptation of Harlan's solution to the OP's pick 6 out of 40 case Define* a name: LottoNumbers referring to: =ROW(INDIRECT("1:40")) *via Insert Name Define Then in A1 enter the formula: =INT(1+COUNT(LottoNumbers)*RAND()) In B1, enter the array formula: =LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumb ers), INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND())) Copy B1 to F1 Select A1:F1, fill down to say, F10 A1:F10 will return 10 sets of 6 numbers (from 1-40) w/o duplicates in any one set Press F9 to recalc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#38
|
|||
|
|||
"Ragdyer" wrote:
Do you realize that you have made it necessary for me to now go out and buy a hat!<g Ah, .. but what better reason can there be ? <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#39
|
|||
|
|||
Thanks for effort on my issue ..
Pleasure`, Steved. You've now got a good number of options to choose from <g -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#40
|
|||
|
|||
Thankyou Max
I put in =AND(A1:F1) on each line perfect no doubles. "Max" wrote: "RagDyeR" wrote: .. Therefore, my hat is off to Harlan if he can accomplish this scenario *without* the existence of this 40 column by 10 row array of random numbers. Think Harlan did accomplish this a few years back ? <g (and in a very concise set-up, too !) re his response at: http://tinyurl.com/b2oan Here's an adaptation of Harlan's solution to the OP's pick 6 out of 40 case Define* a name: LottoNumbers referring to: =ROW(INDIRECT("1:40")) *via Insert Name Define Then in A1 enter the formula: =INT(1+COUNT(LottoNumbers)*RAND()) In B1, enter the array formula: =LARGE(IF(COUNTIF($A1:A1,LottoNumbers),0,LottoNumb ers), INT(1+(COUNT(LottoNumbers)-COUNT($A1:A1))*RAND())) Copy B1 to F1 Select A1:F1, fill down to say, F10 A1:F10 will return 10 sets of 6 numbers (from 1-40) w/o duplicates in any one set Press F9 to recalc -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
random numbers, how can you utilize it without repeating them | Excel Discussion (Misc queries) | |||
random numbers from a data of numbers? | Excel Worksheet Functions | |||
Validating random numbers | Excel Worksheet Functions | |||
Random Numbers Change? | Excel Worksheet Functions |