random number generated based on empirical distribution

[michellex]

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

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.

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

 

[ChrisM]

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.

 

[michellex]

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.

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

 

[shg]

[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)

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

 

[michellex]

shg
Many thanks for your help!

 

[shg]

You're welcome.