If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Random numbers | john liem | Worksheet Functions | 11 | October 11th, 2005 11:06 PM |
GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE | Tracker | General Discussion | 6 | August 5th, 2005 02:49 AM |
how are random numbers determined | hectorvector | Worksheet Functions | 2 | June 10th, 2005 01:55 PM |
If I create a random list in Excel, does it repeat numbers? | Kelly | Worksheet Functions | 2 | March 30th, 2005 04:05 AM |
Random Number Generator | Rich | Worksheet Functions | 4 | August 17th, 2004 07:19 PM |