random number generated based on empirical distribution

M

michellex

New Member
Joined
Jun 8, 2013
Messages
10
Hello, I posted this thread a few days earlier and got help from mirabeau and shg. There are two columns. The second column is the probability of returning the number in the first column.
sample:

[TABLE="class: cms_table"]
<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]


I want a VBA code to generate random numbers based on these probabilities. mirabeau coded this using VBA sub as follows

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

I modified this code and made it a function as follows. So i can use the function in the calculation of other functions.
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
The function works well in the immediate window. it returns random numbers based on the empirical distribution. However, when i use the function in the worksheet, it returns 0 always.
Can anyone tell me where has gone wrong?

Many thanks
Michelle
 
Last edited:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
You are trying to return an array from your function. This requires two things, 1) you enter the formulas as an array with ctrl-shift-enter 2) you have to specify the range of cells that the array will be output to, and it needs to match the size of your rcap array. So if rcap is a one dimensional array of 20 rows, then in your worksheet highlight cells A1:A20, then enter the array formula in A1 with ctrl-shift-enter.

In your case the size of rcap is determined by the Range("A1").CurrentRegion command, which implies you do not know the full range size ahead of time, so I'm not sure how you're going to get around requirement #2. I'm not a UDF expert though, maybe someone else will have an idea.
 
Upvote 0
ChrisM said:
You are trying to return an array from your function. This requires two things, 1) you enter the formulas as an array with ctrl-shift-enter 2) you have to specify the range of cells that the array will be output to, and it needs to match the size of your rcap array. So if rcap is a one dimensional array of 20 rows, then in your worksheet highlight cells A1:A20, then enter the array formula in A1 with ctrl-shift-enter.

In your case the size of rcap is determined by the Range("A1").CurrentRegion command, which implies you do not know the full range size ahead of time, so I'm not sure how you're going to get around requirement #2. I'm not a UDF expert though, maybe someone else will have an idea.
Click to expand...

Code: 
Function rcap() As Double 
    Dim a 
    Dim j As Long 
    Dim x As Double, s As Double 
    a = Sheets("prob").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
I only want to return a specific number in the array. so when the if condition is satisfied, rcap will equal to the number (j,1) in array a. So how to modify it?

Thx
 
Upvote 0
[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.005681818​
[/td][td]
0.000000000​
[/td][td][/td][td]
71​
[/td][td]E2: =rcap(A2:C73)[/td][/tr]

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

[tr][td]
4​
[/td][td]
30​
[/td][td]
0.005681819​
[/td][td]
0.011363636​
[/td][td][/td][td][/td][td][/td][/tr]

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

[tr][td]
6​
[/td][td]
54​
[/td][td]
0.005681818​
[/td][td]
0.028409091​
[/td][td][/td][td][/td][td][/td][/tr]

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

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

[tr][td]
9​
[/td][td]
58​
[/td][td]
0.011363637​
[/td][td]
0.045454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
10​
[/td][td]
63​
[/td][td]
0.011363636​
[/td][td]
0.056818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
11​
[/td][td]
66​
[/td][td]
0.005681818​
[/td][td]
0.068181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
12​
[/td][td]
68​
[/td][td]
0.011363637​
[/td][td]
0.073863636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
13​
[/td][td]
70​
[/td][td]
0.022727272​
[/td][td]
0.085227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
14​
[/td][td]
71​
[/td][td]
0.005681819​
[/td][td]
0.107954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
15​
[/td][td]
72​
[/td][td]
0.005681818​
[/td][td]
0.113636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
16​
[/td][td]
74​
[/td][td]
0.011363636​
[/td][td]
0.119318182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
17​
[/td][td]
75​
[/td][td]
0.005681818​
[/td][td]
0.130681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
18​
[/td][td]
78​
[/td][td]
0.017045455​
[/td][td]
0.136363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
19​
[/td][td]
79​
[/td][td]
0.017045454​
[/td][td]
0.153409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
20​
[/td][td]
80​
[/td][td]
0.005681819​
[/td][td]
0.170454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
21​
[/td][td]
81​
[/td][td]
0.028409091​
[/td][td]
0.176136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
22​
[/td][td]
82​
[/td][td]
0.011363636​
[/td][td]
0.204545455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
23​
[/td][td]
84​
[/td][td]
0.005681818​
[/td][td]
0.215909091​
[/td][td][/td][td][/td][td][/td][/tr]

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

[tr][td]
25​
[/td][td]
86​
[/td][td]
0.011363637​
[/td][td]
0.227272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
26​
[/td][td]
87​
[/td][td]
0.017045454​
[/td][td]
0.238636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
27​
[/td][td]
88​
[/td][td]
0.005681818​
[/td][td]
0.255681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
28​
[/td][td]
89​
[/td][td]
0.011363637​
[/td][td]
0.261363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
29​
[/td][td]
90​
[/td][td]
0.011363636​
[/td][td]
0.272727273​
[/td][td][/td][td][/td][td][/td][/tr]

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

[tr][td]
31​
[/td][td]
92​
[/td][td]
0.011363637​
[/td][td]
0.295454545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
32​
[/td][td]
93​
[/td][td]
0.022727273​
[/td][td]
0.306818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
33​
[/td][td]
94​
[/td][td]
0.034090909​
[/td][td]
0.329545455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
34​
[/td][td]
95​
[/td][td]
0.028409091​
[/td][td]
0.363636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
35​
[/td][td]
96​
[/td][td]
0.034090909​
[/td][td]
0.392045455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
36​
[/td][td]
97​
[/td][td]
0.017045454​
[/td][td]
0.426136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
37​
[/td][td]
98​
[/td][td]
0.022727273​
[/td][td]
0.443181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
38​
[/td][td]
99​
[/td][td]
0.017045454​
[/td][td]
0.465909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
39​
[/td][td]
100​
[/td][td]
0.011363637​
[/td][td]
0.482954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
40​
[/td][td]
101​
[/td][td]
0.017045454​
[/td][td]
0.494318182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
41​
[/td][td]
102​
[/td][td]
0.017045455​
[/td][td]
0.511363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
42​
[/td][td]
103​
[/td][td]
0.034090909​
[/td][td]
0.528409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
43​
[/td][td]
104​
[/td][td]
0.022727273​
[/td][td]
0.562500000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
44​
[/td][td]
105​
[/td][td]
0.011363636​
[/td][td]
0.585227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
45​
[/td][td]
106​
[/td][td]
0.017045455​
[/td][td]
0.596590909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
46​
[/td][td]
107​
[/td][td]
0.017045454​
[/td][td]
0.613636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
47​
[/td][td]
108​
[/td][td]
0.005681818​
[/td][td]
0.630681818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
48​
[/td][td]
109​
[/td][td]
0.028409091​
[/td][td]
0.636363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
49​
[/td][td]
110​
[/td][td]
0.017045455​
[/td][td]
0.664772727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
50​
[/td][td]
111​
[/td][td]
0.028409091​
[/td][td]
0.681818182​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
51​
[/td][td]
112​
[/td][td]
0.039772727​
[/td][td]
0.710227273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
52​
[/td][td]
113​
[/td][td]
0.011363636​
[/td][td]
0.750000000​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
53​
[/td][td]
114​
[/td][td]
0.017045455​
[/td][td]
0.761363636​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
54​
[/td][td]
115​
[/td][td]
0.022727273​
[/td][td]
0.778409091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
55​
[/td][td]
116​
[/td][td]
0.017045454​
[/td][td]
0.801136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
56​
[/td][td]
117​
[/td][td]
0.022727273​
[/td][td]
0.818181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
57​
[/td][td]
118​
[/td][td]
0.011363636​
[/td][td]
0.840909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
58​
[/td][td]
119​
[/td][td]
0.005681818​
[/td][td]
0.852272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
59​
[/td][td]
121​
[/td][td]
0.017045455​
[/td][td]
0.857954545​
[/td][td][/td][td][/td][td][/td][/tr]

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

[tr][td]
61​
[/td][td]
124​
[/td][td]
0.005681818​
[/td][td]
0.892045455​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
62​
[/td][td]
125​
[/td][td]
0.011363636​
[/td][td]
0.897727273​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
63​
[/td][td]
129​
[/td][td]
0.005681818​
[/td][td]
0.909090909​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
64​
[/td][td]
130​
[/td][td]
0.011363637​
[/td][td]
0.914772727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
65​
[/td][td]
131​
[/td][td]
0.017045454​
[/td][td]
0.926136364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
66​
[/td][td]
132​
[/td][td]
0.011363637​
[/td][td]
0.943181818​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
67​
[/td][td]
133​
[/td][td]
0.005681818​
[/td][td]
0.954545455​
[/td][td][/td][td][/td][td][/td][/tr]

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

[tr][td]
69​
[/td][td]
139​
[/td][td]
0.011363636​
[/td][td]
0.965909091​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
70​
[/td][td]
140​
[/td][td]
0.005681818​
[/td][td]
0.977272727​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
71​
[/td][td]
141​
[/td][td]
0.005681819​
[/td][td]
0.982954545​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
72​
[/td][td]
142​
[/td][td]
0.005681818​
[/td][td]
0.988636364​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]
73​
[/td][td]
151​
[/td][td]
0.005681818​
[/td][td]
0.994318182​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

B2 and down are inputs

C2 and down: =SUM(B1:C1)

Code: 
Function rcap(rInp As Range) As Variant
    Application.Volatile
    Randomize
    
    With WorksheetFunction
        rcap = .Index(.Index(rInp, 0, 1), .Match(Rnd(), .Index(rInp, 0, 3)))
    End With
End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Generate 1X2 combinations by percentage.
Replies
9
Views
522
motilulla
M
K0st4din
  • Solved
Macros Serial number generation with repeatability comparison base
2 3
Replies
20
Views
759
StephenCrump
S
D
  • Question Question
QRCode Generator
Replies
1
Views
2K
drag1c
D
JenniferMurphy
  • Solved
Does anyone know of an efficient way to select M numbers from a list of 1 to N randomly?
Replies
5
Views
512
bebo021999
B
E
  • Question Question
Search function not finding rows containing the matching value
Replies
1
Views
654
GlennUK
G

Forum statistics

Threads
1,222,115
Messages
6,164,026
Members
451,867
Latest member
csktwyr

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