VBA function to generate random numbers based on given probability distribution

M

michellex

New Member
Joined
Jun 8, 2013
Messages
10
Hello

I have several probability tables of variables. I want to generate random numbers based on the probabilities. I used the function below to generate random numbers. However, this function took a long time to manually type every probability, and I got several variables which need to be generated. Can anyone help me to improve the function so that it can read the probabilities directly from the table or are there other ways to generate random numbers based on empirical distribution?

sample:

[TABLE="width: 314"]
<tbody>[TR]
[TD][/TD]
[TD]probability[/TD]
[TD]accumulate probability[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.005681818[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.011363636[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.017045455[/TD]
[/TR]
[TR]
[TD="align: right"]51[/TD]
[TD="align: right"]0.011363636[/TD]
[TD="align: right"]0.028409091[/TD]
[/TR]
[TR]
[TD="align: right"]54[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.034090909[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.039772727[/TD]
[/TR]
[TR]
[TD="align: right"]57[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.045454545[/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD="align: right"]0.011363636[/TD]
[TD="align: right"]0.056818182[/TD]
[/TR]
[TR]
[TD="align: right"]63[/TD]
[TD="align: right"]0.011363636[/TD]
[TD="align: right"]0.068181818[/TD]
[/TR]
[TR]
[TD="align: right"]66[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.073863636[/TD]
[/TR]
[TR]
[TD="align: right"]68[/TD]
[TD="align: right"]0.011363636[/TD]
[TD="align: right"]0.085227273[/TD]
[/TR]
[TR]
[TD="align: right"]70[/TD]
[TD="align: right"]0.022727273[/TD]
[TD="align: right"]0.107954545[/TD]
[/TR]
[TR]
[TD="align: right"]71[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.113636364[/TD]
[/TR]
[TR]
[TD="align: right"]72[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.119318182[/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD="align: right"]0.011363636[/TD]
[TD="align: right"]0.130681818[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.136363636[/TD]
[/TR]
[TR]
[TD="align: right"]78[/TD]
[TD="align: right"]0.017045455[/TD]
[TD="align: right"]0.153409091[/TD]
[/TR]
[TR]
[TD="align: right"]79[/TD]
[TD="align: right"]0.017045455[/TD]
[TD="align: right"]0.170454545[/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]0.005681818[/TD]
[TD="align: right"]0.176136364[/TD]
[/TR]
</tbody>[/TABLE]
Code: 
Function CapacityP() As Double
    Randomize
    Dim r As Double
    
    r = Rnd
    Select Case r
    Case 0 To 0.005681818
        CapacityP = 21
    Case 0.005681818 To 0.011363636
        CapacityP = 28
    Case 0.011363636 To 0.017045455
        CapacityP = 30
    Case 0.017045455 To 0.028409091
        CapacityP = 51
    Case 0.028409091 To 0.034090909
        CapacityP = 54
    Case 0.034090909 To 0.039772727
        CapacityP = 55
    Case 0.039772727 To 0.045454545
        CapacityP = 57
    Case 0.045454545 To 0.056818182
        CapacityP = 58
    Case 0.056818182 To 0.068181818
        CapacityP = 63
    Case 0.068181818 To 0.073863636
        CapacityP = 66
    Case 0.073863636 To 0.085227273
        CapacityP = 68
    Case 0.085227273 To 0.107954545
        CapacityP = 70
    Case 0.107954545 To 0.113636364
        CapacityP = 71
    Case 0.113636364 To 0.119318182
        CapacityP = 72
    Case 0.119318182 To 0.130681818
        CapacityP = 74
    Case 0.130681818 To 0.136363636
        CapacityP = 75
    Case 0.136363636 To 0.153409091
        CapacityP = 78
    Case 0.153409091 To 0.170454545
        CapacityP = 79
    Case 0.170454545 To 0.176136364
        CapacityP = 80
    Case 0.176136364 To 0.204545455
        CapacityP = 81
    Case 0.204545455 To 0.215909091
        CapacityP = 82
    Case 0.215909091 To 0.221590909
        CapacityP = 84
    Case 0.221590909 To 0.227272727
        CapacityP = 85
    Case 0.227272727 To 0.238636364
        CapacityP = 86
    Case 0.238636364 To 0.255681818
        CapacityP = 87
    Case 0.255681818 To 0.261363636
        CapacityP = 88
    Case 0.261363636 To 0.272727273
        CapacityP = 89
    Case 0.272727273 To 0.284090909
        CapacityP = 90
    Case 0.284090909 To 0.295454545
        CapacityP = 91
    Case 0.295454545 To 0.306818182
        CapacityP = 92
    Case 0.306818182 To 0.329545455
        CapacityP = 93
    Case 0.329545455 To 0.363636364
        CapacityP = 94
    Case 0.363636364 To 0.392045455
        CapacityP = 95
    Case 0.392045455 To 0.426136364
        CapacityP = 96
    Case 0.426136364 To 0.443181818
        CapacityP = 97
    Case 0.443181818 To 0.465909091
        CapacityP = 98
    Case 0.465909091 To 0.482954545
        CapacityP = 99
    Case 0.482954545 To 0.494318182
        CapacityP = 100
    Case 0.494318182 To 0.511363636
        CapacityP = 101
    Case 0.511363636 To 0.528409091
        CapacityP = 102
    Case 0.528409091 To 0.5625
        CapacityP = 103
    Case 0.5625 To 0.585227273
        CapacityP = 104
    Case 0.585227273 To 0.596590909
        CapacityP = 105
    Case 0.596590909 To 0.613636364
        CapacityP = 106
    Case 0.613636364 To 0.630681818
        CapacityP = 107
    Case 0.630681818 To 0.636363636
        CapacityP = 108
    Case 0.636363636 To 0.664772727
        CapacityP = 109
    Case 0.664772727 To 0.681818182
        CapacityP = 110
    Case 0.681818182 To 0.710227273
        CapacityP = 111
    Case 0.710227273 To 0.75
        CapacityP = 112
    Case 0.75 To 0.761363636
        CapacityP = 113
    Case 0.761363636 To 0.778409091
        CapacityP = 114
    Case 0.778409091 To 0.801136364
        CapacityP = 115
    Case 0.801136364 To 0.818181818
        CapacityP = 116
    Case 0.818181818 To 0.840909091
        CapacityP = 117
    Case 0.840909091 To 0.852272727
        CapacityP = 118
    Case 0.852272727 To 0.857954545
        CapacityP = 119
    Case 0.857954545 To 0.875
        CapacityP = 121
    Case 0.875 To 0.892045455
        CapacityP = 122
    Case 0.892045455 To 0.897727273
        CapacityP = 124
    Case 0.897727273 To 0.909090909
        CapacityP = 125
    Case 0.909090909 To 0.914772727
        CapacityP = 129
    Case 0.914772727 To 0.926136364
        CapacityP = 130
    Case 0.926136364 To 0.943181818
        CapacityP = 131
    Case 0.943181818 To 0.954545455
        CapacityP = 132
    Case 0.954545455 To 0.960227273
        CapacityP = 133
    Case 0.960227273 To 0.965909091
        CapacityP = 135
    Case 0.965909091 To 0.977272727
        CapacityP = 139
    Case 0.977272727 To 0.982954545
        CapacityP = 140
    Case 0.982954545 To 0.988636364
        CapacityP = 141
    Case 0.988636364 To 0.994318182
        CapacityP = 142
    Case 0.994318182 To 1
        CapacityP = 151
    Case Else
        CapacityP = 0
    End Select
End Function



Many thanks
Michelle
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
hi Michelle,

here is a VBA code that may be of some help to you.

to use it, first list your (discrete) distribution in columns A and B of a blank worksheet

Specifically, put your set of numbers (say 2, 7, 9, ... as many as you like, preferably in increasing order) from A1 downwards (no gaps) and the probabilities associated with each number from B1 downwards (say 0.7, 0.1 and 0.2).
So
P(2) = 0.7
P(7) = 0.1
P(9) = 0.2

Then run the code.
The output should hopefully be self-explanatory
Code: 
Sub random_distr()

Const n& = 1000 'change as required
'n is how many numbers satifying the distribution that you want listed
Dim numbers, probs
Dim c As Long, j As Long, r As Long
Dim s As Double, x As Double
Dim d As Object
Set d = CreateObject("scripting.dictionary")

r = Cells(Rows.Count, 1).End(3).Row
numbers = Range("A1").Resize(r)
probs = Range("B1").Resize(r)
Randomize
For c = 1 To n
    s = 0: x = Rnd
    For j = 1 To r
        s = s + probs(j, 1)
        If x <= s Then
            Cells(c + 1, "d") = numbers(j, 1)
            d(numbers(j, 1)) = d(numbers(j, 1)) + 1
            Exit For
        End If
Next j, c

[f1].Resize(, 2) = Array("Number", "Frequency")
With [f2].Resize(d.Count, 2)
    .Value = Application.Transpose(Array(d.keys, d.items))
    .Sort [f2], 1, Header:=xlNo
End With
With [g2].Resize(d.Count)
    .Cells = Evaluate(.Address & "/" & n)
    .NumberFormat = "0.00"
End With
[d1] = "Distrib"
[d:g].EntireColumn.AutoFit

End Sub
 
Upvote 0
You could instead just use a simple formula:

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr]
[tr][td]
1​
[/td][td]
Value​
[/td][td]
Prob​
[/td][td]
Cum​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
2​
[/td][td]
21​
[/td][td]
0.000000000​
[/td][td]
0.000000000​
[/td][td][/td][td]
132​
[/td][td]E2: =INDEX($A$2:$A$73, MATCH(RAND(), $C$2:$C$73))[/td][/tr]

[tr][td]
3​
[/td][td]
28​
[/td][td]
0.005681818​
[/td][td]
0.000000000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
4​
[/td][td]
30​
[/td][td]
0.005681818​
[/td][td]
0.005681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
5​
[/td][td]
51​
[/td][td]
0.005681819​
[/td][td]
0.011363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
6​
[/td][td]
54​
[/td][td]
0.011363636​
[/td][td]
0.017045455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]
55​
[/td][td]
0.005681818​
[/td][td]
0.028409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
8​
[/td][td]
57​
[/td][td]
0.005681818​
[/td][td]
0.034090909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
9​
[/td][td]
58​
[/td][td]
0.005681818​
[/td][td]
0.039772727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
63​
[/td][td]
0.011363637​
[/td][td]
0.045454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
66​
[/td][td]
0.011363636​
[/td][td]
0.056818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
68​
[/td][td]
0.005681818​
[/td][td]
0.068181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
70​
[/td][td]
0.011363637​
[/td][td]
0.073863636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td]
71​
[/td][td]
0.022727272​
[/td][td]
0.085227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td]
72​
[/td][td]
0.005681819​
[/td][td]
0.107954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td]
74​
[/td][td]
0.005681818​
[/td][td]
0.113636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]
75​
[/td][td]
0.011363636​
[/td][td]
0.119318182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]
78​
[/td][td]
0.005681818​
[/td][td]
0.130681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
19​
[/td][td]
79​
[/td][td]
0.017045455​
[/td][td]
0.136363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
20​
[/td][td]
80​
[/td][td]
0.017045454​
[/td][td]
0.153409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
21​
[/td][td]
81​
[/td][td]
0.005681819​
[/td][td]
0.170454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
22​
[/td][td]
82​
[/td][td]
0.028409091​
[/td][td]
0.176136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
23​
[/td][td]
84​
[/td][td]
0.011363636​
[/td][td]
0.204545455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
24​
[/td][td]
85​
[/td][td]
0.005681818​
[/td][td]
0.215909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
25​
[/td][td]
86​
[/td][td]
0.005681818​
[/td][td]
0.221590909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
26​
[/td][td]
87​
[/td][td]
0.011363637​
[/td][td]
0.227272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
27​
[/td][td]
88​
[/td][td]
0.017045454​
[/td][td]
0.238636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
28​
[/td][td]
89​
[/td][td]
0.005681818​
[/td][td]
0.255681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
29​
[/td][td]
90​
[/td][td]
0.011363637​
[/td][td]
0.261363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
30​
[/td][td]
91​
[/td][td]
0.011363636​
[/td][td]
0.272727273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
31​
[/td][td]
92​
[/td][td]
0.011363636​
[/td][td]
0.284090909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
32​
[/td][td]
93​
[/td][td]
0.011363637​
[/td][td]
0.295454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
33​
[/td][td]
94​
[/td][td]
0.022727273​
[/td][td]
0.306818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
34​
[/td][td]
95​
[/td][td]
0.034090909​
[/td][td]
0.329545455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
35​
[/td][td]
96​
[/td][td]
0.028409091​
[/td][td]
0.363636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
36​
[/td][td]
97​
[/td][td]
0.034090909​
[/td][td]
0.392045455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
37​
[/td][td]
98​
[/td][td]
0.017045454​
[/td][td]
0.426136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
38​
[/td][td]
99​
[/td][td]
0.022727273​
[/td][td]
0.443181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
39​
[/td][td]
100​
[/td][td]
0.017045454​
[/td][td]
0.465909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
40​
[/td][td]
101​
[/td][td]
0.011363637​
[/td][td]
0.482954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
41​
[/td][td]
102​
[/td][td]
0.017045454​
[/td][td]
0.494318182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
42​
[/td][td]
103​
[/td][td]
0.017045455​
[/td][td]
0.511363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
43​
[/td][td]
104​
[/td][td]
0.034090909​
[/td][td]
0.528409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
44​
[/td][td]
105​
[/td][td]
0.022727273​
[/td][td]
0.562500000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
45​
[/td][td]
106​
[/td][td]
0.011363636​
[/td][td]
0.585227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
46​
[/td][td]
107​
[/td][td]
0.017045455​
[/td][td]
0.596590909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
47​
[/td][td]
108​
[/td][td]
0.017045454​
[/td][td]
0.613636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
48​
[/td][td]
109​
[/td][td]
0.005681818​
[/td][td]
0.630681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
49​
[/td][td]
110​
[/td][td]
0.028409091​
[/td][td]
0.636363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
50​
[/td][td]
111​
[/td][td]
0.017045455​
[/td][td]
0.664772727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
51​
[/td][td]
112​
[/td][td]
0.028409091​
[/td][td]
0.681818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
52​
[/td][td]
113​
[/td][td]
0.039772727​
[/td][td]
0.710227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
53​
[/td][td]
114​
[/td][td]
0.011363636​
[/td][td]
0.750000000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
54​
[/td][td]
115​
[/td][td]
0.017045455​
[/td][td]
0.761363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
55​
[/td][td]
116​
[/td][td]
0.022727273​
[/td][td]
0.778409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
56​
[/td][td]
117​
[/td][td]
0.017045454​
[/td][td]
0.801136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
57​
[/td][td]
118​
[/td][td]
0.022727273​
[/td][td]
0.818181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
58​
[/td][td]
119​
[/td][td]
0.011363636​
[/td][td]
0.840909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
59​
[/td][td]
121​
[/td][td]
0.005681818​
[/td][td]
0.852272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
60​
[/td][td]
122​
[/td][td]
0.017045455​
[/td][td]
0.857954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
61​
[/td][td]
124​
[/td][td]
0.017045455​
[/td][td]
0.875000000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
62​
[/td][td]
125​
[/td][td]
0.005681818​
[/td][td]
0.892045455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
63​
[/td][td]
129​
[/td][td]
0.011363636​
[/td][td]
0.897727273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
64​
[/td][td]
130​
[/td][td]
0.005681818​
[/td][td]
0.909090909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
65​
[/td][td]
131​
[/td][td]
0.011363637​
[/td][td]
0.914772727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
66​
[/td][td]
132​
[/td][td]
0.017045454​
[/td][td]
0.926136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
67​
[/td][td]
133​
[/td][td]
0.011363637​
[/td][td]
0.943181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
68​
[/td][td]
135​
[/td][td]
0.005681818​
[/td][td]
0.954545455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
69​
[/td][td]
139​
[/td][td]
0.005681818​
[/td][td]
0.960227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
70​
[/td][td]
140​
[/td][td]
0.011363636​
[/td][td]
0.965909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
71​
[/td][td]
141​
[/td][td]
0.005681818​
[/td][td]
0.977272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
72​
[/td][td]
142​
[/td][td]
0.005681819​
[/td][td]
0.982954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
73​
[/td][td]
151​
[/td][td]
0.005681818​
[/td][td]
0.988636364​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
I slipped a row in col C, but you get the idea.
 
Upvote 0
just a minor clarification

the VBA code in Post#2 is mostly taken up with calculating and displaying outcome frequencies, as a visual check on the code doing as it should.

to just generate and list random outcomes from the probability distribution listed in Cols A and B is much simpler.

modified version as below does its own cumulating and (for me) listed 100,000 random outcomes of your distribution in about 1/3 sec. but it does require that the listed probabilities sum to 1
Code: 
Sub random_distr_2()

Const n& = 100000 'change as required
Dim a, u()
Dim c As Long, j As Long
Dim x As Double, s As Double
ReDim u(1 To n, 1 To 1)
a = Range("A1").CurrentRegion.Resize(, 2)
Randomize

For c = 1 To n
    s = 0: x = Rnd
    For j = 1 To UBound(a)
        s = s + a(j, 2)
        If x <= s Then
            u(c, 1) = a(j, 1)
            Exit For
        End If
Next j, c
Range("D2").Resize(n) = u

End Sub
 
Upvote 0
Thanks for both of your help.

shg
Your method is easy. however i need to use VBA as it will be used in other code.

mirabeau
Can you modify the code as a function rather than sub. Because the output does not need to be listed and the random number generating function will be used in other code as well.

Thanks
 
Upvote 0
I have modified the code as follows. however it does not work. the function output 0 all the time. Can you have a look where has gone wrong.

Code: 
Function rcap()


Dim a
Dim j As Long
Dim x As Double, s As Double
a = Range("a1").CurrentRegion.Resize(, 2)
Randomize


s = 0: x = Rnd
For j = 1 To UBound(a)
    s = s + a(j, 2)
    If x <= s Then
        rcap = a(j, 1)
        Exit For
    End If
Next j


End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
random number generated based on empirical distribution
Replies
5
Views
2K
shg
shg

Forum statistics

Threads
1,223,626
Messages
6,173,414
Members
452,514
Latest member
cjkelly15

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top