Selecting 7 numbers from a weighted list of numbers

[AndyStanton]

Hi All,

I'm trying to generate a list of 7 random numbers from a list of 45 numbers which each have a previous frequency of selection. I'm trying to weight the random numbers to the previous frequency. Any assistance would be great!

Kind Regards,
Andy

Number	Frequency	Frequency % of total	Cumulative
33	149	0.025627795	0.025628
19	144	0.024767802	0.050396
40	144	0.024767802	0.075163
25	143	0.024595803	0.099759
2	141	0.024251806	0.124011
7	140	0.024079807	0.148091
28	139	0.023907809	0.171999
35	139	0.023907809	0.195906
1	138	0.02373581	0.219642
21	138	0.02373581	0.243378
20	137	0.023563811	0.266942
27	136	0.023391813	0.290334
37	136	0.023391813	0.313725
3	135	0.023219814	0.336945
5	135	0.023219814	0.360165
6	134	0.023047816	0.383213
45	134	0.023047816	0.406261
8	133	0.022875817	0.429137
16	133	0.022875817	0.452012
26	133	0.022875817	0.474888
43	133	0.022875817	0.497764
4	131	0.02253182	0.520296
17	131	0.02253182	0.542828
41	131	0.02253182	0.565359
22	130	0.022359821	0.587719
44	130	0.022359821	0.610079
15	129	0.022187822	0.632267
23	129	0.022187822	0.654455
29	129	0.022187822	0.676643
34	128	0.022015824	0.698658
9	125	0.021499828	0.720158
12	125	0.021499828	0.741658
13	125	0.021499828	0.763158
31	124	0.021327829	0.784486
39	123	0.021155831	0.805642
36	122	0.020983832	0.826625
32	118	0.020295838	0.846921
38	116	0.01995184	0.866873
14	115	0.019779842	0.886653
18	113	0.019435845	0.906089
30	113	0.019435845	0.925525
42	111	0.019091847	0.944616
11	110	0.018919849	0.963536
24	110	0.018919849	0.982456
10	102	0.01754386	1

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[ranman256]

Public Sub RandomN()
Dim colNames As New Collection
Const kPICKS = 7
Dim i As Integer, iCnt As Integer, iNum As Integer, iCol As Integer
Dim rStart As Range


On Error GoTo errRand


    'load the collection
Sheets("sheet1").Select
Range("A2").Select
While ActiveCell.Value <> ""
   colNames.Add ActiveCell.Value
   ActiveCell.Offset(1, 0).Select   'next row
Wend


  'post results
Sheets.Add
Set rStart = Range("A1")
    For i = 1 To kPICKS
      iCnt = colNames.Count
      'iNum = Int(iCnt * Rnd) + 1
      iNum = Int(1 + Rnd() * (iCnt - 1 + 1))
      
      ActiveCell.Value = colNames(iNum)
      colNames.Remove iNum
      
       ActiveCell.Offset(1, 0).Select   'next row
    Next
    
    iCol = iCol + 1
    rStart.Offset(0, iCol).Select
errRand:
Set rStart = Nothing
End Sub

 

[AndyStanton]

Thanks ranman256,

I'm not quite sure how to implement your code/recommendation - how would I do this in Excel?

 

[shg]

	A​	B​	C​	D​
1​	Number​	Freq​	Cumu​	​
2​	33​	149​	0​	C2: =SUM(B$1:B1)/SUM($B$2:$B$46)
3​	19​	144​	0.025628​
4​	40​	144​	0.050396​
5​	25​	143​	0.075163​
6​	2​	141​	0.099759​
7​	7​	140​	0.124011​
8​	28​	139​	0.148091​
9​	35​	139​	0.171999​
10​	1​	138​	0.195906​
11​	21​	138​	0.219642​
12​	20​	137​	0.243378​
13​	27​	136​	0.266942​
14​	37​	136​	0.290334​
15​	3​	135​	0.313725​
16​	5​	135​	0.336945​
17​	6​	134​	0.360165​
18​	45​	134​	0.383213​
19​	8​	133​	0.406261​
20​	16​	133​	0.429137​
21​	26​	133​	0.452012​
22​	43​	133​	0.474888​
23​	4​	131​	0.497764​
24​	17​	131​	0.520296​
25​	41​	131​	0.542828​
26​	22​	130​	0.565359​
27​	44​	130​	0.587719​
28​	15​	129​	0.610079​
29​	23​	129​	0.632267​
30​	29​	129​	0.654455​
31​	34​	128​	0.676643​
32​	9​	125​	0.698658​
33​	12​	125​	0.720158​
34​	13​	125​	0.741658​
35​	31​	124​	0.763158​
36​	39​	123​	0.784486​
37​	36​	122​	0.805642​
38​	32​	118​	0.826625​
39​	38​	116​	0.846921​
40​	14​	115​	0.866873​
41​	18​	113​	0.886653​
42​	30​	113​	0.906089​
43​	42​	111​	0.925525​
44​	11​	110​	0.944616​
45​	24​	110​	0.963536​
46​	10​	102​	0.982456​
47​
48​	19​			A48: =INDEX($A$2:$A$46, MATCH(RAND(), $C$2:$C$46))
49​	7​
50​	42​
51​	17​
52​	40​
53​	41​
54​	2​
55​	18​
56​	13​
57​	37​
58​	2​
59​	45​
60​	7​
61​	32​
62​	19​
63​	37​

 

[AndyStanton]

Thanks shg,

For some reason when I use INDEX($A$2:$A$46, MATCH(RAND(), $C$2:$C$46)) it seems to only come up with 10 each time - any ideas?

 

[shg]

File > Options > Formulas, > Calculation options > Workbook calculation, tick Automatic

 

[AndyStanton]

Thanks! I realised I had it pointing to Frequency % of total rather than cumulative

 

[shg]

Glad you got it sorted.