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 |
#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 -- |
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 |