Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation from a set of non-sequencial values
I'm not sure that Excel can do this, and I've never needed to try anything
like it in the past, so I'm not even sure how to attempt it. Basically, I have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17, 19, 20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I want to generate sets of 5 or 6 random numbers from among them, and ONLY from among those number. The random number generation function native to Excel ( RAND()*(b-a)+a or RAND()*100 ) would give me a true random number, or random number from 0-100 for example, but is there any way I could exclude all but specific numbers from the value range and have it pick 5 values from my set as I described? Hopefully I've described what I'm after well enough that it makes sense. Thank you in advance. KM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation from a set of non-sequencial values
Hi KM
The most common way to make this "excel lottery" is to have the contestants (here your numbers) downwards in A column, and formula =RAND() in B column. Sort by B, top n in A are winners. There are also code solutions. Here is a simple one, replace "sName" assignments and number of contestants with something useful: Type Man sName As String LNumber As Double End Type Sub Draw() Dim ThisMan As Man Dim Men() As Man Dim L As Long, M As Long Randomize 'read: ReDim Men(1 To 6) Men(1).sName = "A" Men(1).LNumber = Rnd() Men(2).sName = "B" Men(2).LNumber = Rnd() Men(3).sName = "C" Men(3).LNumber = Rnd() Men(4).sName = "D" Men(4).LNumber = Rnd() Men(5).sName = "E" Men(5).LNumber = Rnd() Men(6).sName = "F" Men(6).LNumber = Rnd() 'sort: For L = 1 To 5 For M = 1 To 5 If Men(M).LNumber Men(M + 1).LNumber Then ThisMan.LNumber = Men(M + 1).LNumber ThisMan.sName = Men(M + 1).sName Men(M + 1).LNumber = Men(M).LNumber Men(M + 1).sName = Men(M).sName Men(M).LNumber = ThisMan.LNumber Men(M).sName = ThisMan.sName End If Next M Next L 'prompt: MsgBox "Winner is " & Men(6).sName MsgBox "#2 is " & Men(5).sName End Sub HTH. Best wishes Harald "KevinMyers" skrev i melding news:Mnxvd.5075$2r.2285@fed1read02... I'm not sure that Excel can do this, and I've never needed to try anything like it in the past, so I'm not even sure how to attempt it. Basically, I have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17, 19, 20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I want to generate sets of 5 or 6 random numbers from among them, and ONLY from among those number. The random number generation function native to Excel ( RAND()*(b-a)+a or RAND()*100 ) would give me a true random number, or random number from 0-100 for example, but is there any way I could exclude all but specific numbers from the value range and have it pick 5 values from my set as I described? Hopefully I've described what I'm after well enough that it makes sense. Thank you in advance. KM |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation from a set of non-sequencial values
Excellent! Thanks so much, Harald. The first option is so simple and it's
perfect for the quicky job I'm doing. Thanks again. KM "Harald Staff" wrote in message ... Hi KM The most common way to make this "excel lottery" is to have the contestants (here your numbers) downwards in A column, and formula =RAND() in B column. Sort by B, top n in A are winners. There are also code solutions. Here is a simple one, replace "sName" assignments and number of contestants with something useful: Type Man sName As String LNumber As Double End Type Sub Draw() Dim ThisMan As Man Dim Men() As Man Dim L As Long, M As Long Randomize 'read: ReDim Men(1 To 6) Men(1).sName = "A" Men(1).LNumber = Rnd() Men(2).sName = "B" Men(2).LNumber = Rnd() Men(3).sName = "C" Men(3).LNumber = Rnd() Men(4).sName = "D" Men(4).LNumber = Rnd() Men(5).sName = "E" Men(5).LNumber = Rnd() Men(6).sName = "F" Men(6).LNumber = Rnd() 'sort: For L = 1 To 5 For M = 1 To 5 If Men(M).LNumber Men(M + 1).LNumber Then ThisMan.LNumber = Men(M + 1).LNumber ThisMan.sName = Men(M + 1).sName Men(M + 1).LNumber = Men(M).LNumber Men(M + 1).sName = Men(M).sName Men(M).LNumber = ThisMan.LNumber Men(M).sName = ThisMan.sName End If Next M Next L 'prompt: MsgBox "Winner is " & Men(6).sName MsgBox "#2 is " & Men(5).sName End Sub HTH. Best wishes Harald "KevinMyers" skrev i melding news:Mnxvd.5075$2r.2285@fed1read02... I'm not sure that Excel can do this, and I've never needed to try anything like it in the past, so I'm not even sure how to attempt it. Basically, I have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17, 19, 20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I want to generate sets of 5 or 6 random numbers from among them, and ONLY from among those number. The random number generation function native to Excel ( RAND()*(b-a)+a or RAND()*100 ) would give me a true random number, or random number from 0-100 for example, but is there any way I could exclude all but specific numbers from the value range and have it pick 5 values from my set as I described? Hopefully I've described what I'm after well enough that it makes sense. Thank you in advance. KM |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random number generation from a set of non-sequencial values
Great. Thanks for the feedback.
Best wishes Harald "KevinMyers" skrev i melding news:XHxvd.5077$2r.2359@fed1read02... Excellent! Thanks so much, Harald. The first option is so simple and it's perfect for the quicky job I'm doing. Thanks again. KM "Harald Staff" wrote in message ... Hi KM The most common way to make this "excel lottery" is to have the contestants (here your numbers) downwards in A column, and formula =RAND() in B column. Sort by B, top n in A are winners. There are also code solutions. Here is a simple one, replace "sName" assignments and number of contestants with something useful: Type Man sName As String LNumber As Double End Type Sub Draw() Dim ThisMan As Man Dim Men() As Man Dim L As Long, M As Long Randomize 'read: ReDim Men(1 To 6) Men(1).sName = "A" Men(1).LNumber = Rnd() Men(2).sName = "B" Men(2).LNumber = Rnd() Men(3).sName = "C" Men(3).LNumber = Rnd() Men(4).sName = "D" Men(4).LNumber = Rnd() Men(5).sName = "E" Men(5).LNumber = Rnd() Men(6).sName = "F" Men(6).LNumber = Rnd() 'sort: For L = 1 To 5 For M = 1 To 5 If Men(M).LNumber Men(M + 1).LNumber Then ThisMan.LNumber = Men(M + 1).LNumber ThisMan.sName = Men(M + 1).sName Men(M + 1).LNumber = Men(M).LNumber Men(M + 1).sName = Men(M).sName Men(M).LNumber = ThisMan.LNumber Men(M).sName = ThisMan.sName End If Next M Next L 'prompt: MsgBox "Winner is " & Men(6).sName MsgBox "#2 is " & Men(5).sName End Sub HTH. Best wishes Harald "KevinMyers" skrev i melding news:Mnxvd.5075$2r.2285@fed1read02... I'm not sure that Excel can do this, and I've never needed to try anything like it in the past, so I'm not even sure how to attempt it. Basically, I have a set of non-sequencial numbers (2, 4, 5, 6, 9, 12, 14, 15, 16, 17, 19, 20, 22, 26, 27, 31, 34, 35, 39, 40, 41, 42, 45, 48, for example), and I want to generate sets of 5 or 6 random numbers from among them, and ONLY from among those number. The random number generation function native to Excel ( RAND()*(b-a)+a or RAND()*100 ) would give me a true random number, or random number from 0-100 for example, but is there any way I could exclude all but specific numbers from the value range and have it pick 5 values from my set as I described? Hopefully I've described what I'm after well enough that it makes sense. Thank you in advance. KM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANDOM NUMBER GENERATION. | Excel Worksheet Functions | |||
random number generation | Excel Discussion (Misc queries) | |||
random number generation | Excel Worksheet Functions | |||
I need help with random number generation | Excel Worksheet Functions | |||
random number generation | Excel Programming |