Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
how do i do to make Excel to generate random numbers to 30 persons?, but one
detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't avoid repeated numbers... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
One play to try ..
List the numbers: 1 - 30 into A1:A30 Put in B1: =RAND() Copy B1 down to B30 Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$30)) Copy C1 down to C30 C1:C30 will return a random shuffle of the numbers: 1-30 (non-repeating) Press F9 to regenerate We could simply replace A1:A30 with whatever 30 unique numbers (other than 1-30) or items that is to randomly shuffled -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kleverton Silva" <Kleverton wrote in message ... how do i do to make Excel to generate random numbers to 30 persons?, but one detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't avoid repeated numbers... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
'This UDF will generate x unique random numbers between any 2 numbers you
specify. Many thanks to J.E. McGimpsey for modifying this to work on more than 10 numbers. 'The Code 'To use this UDF push Alt+F11 and go InsertModule and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below. '=RandLotto(1,20,8) 'This would produce 8 unique random numbers between 1 and 20 Function RandLotto(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 RandLotto = RandLotto & " " & iArr(i) Next i RandLotto = Trim(RandLotto) End Function Works great for me Gilles "Kleverton Silva" <Kleverton wrote in message ... how do i do to make Excel to generate random numbers to 30 persons?, but one detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't avoid repeated numbers... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
both helps, but, on the Max's Advice, the numbers appeared in the same order
they were on Colum A:A... not randomized or shuffled... and on Gilles Desjardins's Advice all the number appeared concatenated in the same cell... but thanks for the help... i'll study both advices to see if i can solve from here... but if you two know how to proceed from here... i'll be pleasure... thanks! "Gilles Desjardins" escreveu: 'This UDF will generate x unique random numbers between any 2 numbers you specify. Many thanks to J.E. McGimpsey for modifying this to work on more than 10 numbers. 'The Code 'To use this UDF push Alt+F11 and go InsertModule and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below. '=RandLotto(1,20,8) 'This would produce 8 unique random numbers between 1 and 20 Function RandLotto(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 RandLotto = RandLotto & " " & iArr(i) Next i RandLotto = Trim(RandLotto) End Function Works great for me Gilles "Kleverton Silva" <Kleverton wrote in message ... how do i do to make Excel to generate random numbers to 30 persons?, but one detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't avoid repeated numbers... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
"Kleverton Silva" wrote
.. on Max's .. the numbers appeared in the same order they were on Colum A:A ... not randomized or shuffled Don't know what happened over there, but here's a sample working construct to play with: http://cjoint.com/?lqhmUmbaFV KlevertonSilva_misc.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
ok... problem solved.. thanks Max!!!
"Max" escreveu: "Kleverton Silva" wrote .. on Max's .. the numbers appeared in the same order they were on Colum A:A ... not randomized or shuffled Don't know what happened over there, but here's a sample working construct to play with: http://cjoint.com/?lqhmUmbaFV KlevertonSilva_misc.xls -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
You're welcome !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kleverton Silva" wrote in message ... ok... problem solved.. thanks Max!!! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomized Functions
Once the numbers are created you just convert: Data, text to columns
Gilles "Kleverton Silva" <Kleverton wrote in message ... how do i do to make Excel to generate random numbers to 30 persons?, but one detail, no repeated numbers! i tried the "=Randbetween(1;30)" but i couldn't avoid repeated numbers... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Changing the range of several averaging functions | Excel Discussion (Misc queries) | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
PASTE DOWN FUNCTIONS | Excel Worksheet Functions |