About Random number generator function

[Ashwini Lambture]

Hi All, I have Columns as

A B C D E
1 Age(18-24) Age(25-34) Age(35-44) Age(45-54) Age(55-Above)
2

I want random number generated in all the columnin 2^nd row i.e. A,B,C,D,E such that whose sum(A+B+C+D+E) will be 100 thanks in advance.

 

[shg]

Skewness is an observation about the asymmetry of a dataset; the polynomial distributions (other than the flat distribution for a single cut) are affirmatively asymmetric. You can calculate it, but the result is not informative. Similarly, kurtosis measures flatness, which is not relevant at all.

Here's a histogram of 5000 numbers generated by 'normalized sums'

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

2​

0.343​

0.848​

0.524​

0.499​

0.793​

11.409​

28.211​

17.413​

16.593​

26.373​

5​

525​

3​

0.684​

0.704​

0.010​

0.279​

0.769​

27.959​

28.786​

0.398​

11.416​

31.441​

10​

607​

4​

0.526​

0.726​

0.014​

0.780​

0.468​

20.919​

28.884​

0.575​

31.015​

18.607​

15​

636​

5​

0.131​

0.508​

0.876​

0.801​

0.382​

4.872​

18.817​

32.453​

29.683​

14.175​

20​

705​

6​

0.358​

0.859​

0.453​

0.251​

0.024​

18.383​

44.153​

23.301​

12.928​

1.235​

25​

833​

7​

0.405​

0.022​

0.813​

0.996​

0.118​

17.201​

0.918​

34.560​

42.316​

5.005​

30​

744​

8​

0.523​

0.156​

0.072​

0.231​

0.203​

44.102​

13.193​

6.060​

19.477​

17.168​

35​

496​

9​

0.679​

0.139​

0.621​

0.870​

0.605​

23.302​

4.772​

21.316​

29.845​

20.764​

40​

256​

10​

0.336​

0.357​

0.095​

0.060​

0.701​

21.697​

23.032​

6.147​

3.890​

45.233​

45​

109​

11​

0.750​

0.664​

0.609​

0.564​

0.736​

22.570​

19.968​

18.328​

16.976​

22.157​

50​

56​

12​

0.440​

0.835​

0.939​

0.597​

0.241​

14.411​

27.355​

30.772​

19.553​

7.910​

55​

18​

13​

0.656​

0.749​

0.986​

0.613​

0.167​

20.681​

23.613​

31.090​

19.341​

5.274​

60​

9​

14​

0.925​

0.240​

0.321​

0.966​

0.105​

36.174​

9.383​

12.565​

37.777​

4.101​

65​

4​

15​

0.808​

0.100​

0.538​

0.545​

0.740​

29.573​

3.665​

19.686​

19.963​

27.113​

70​

2​

16​

0.849​

0.986​

0.733​

0.548​

0.622​

22.719​

26.382​

19.607​

14.655​

16.637​

75​

0​

17​

0.218​

0.725​

0.810​

0.732​

0.298​

7.820​

26.052​

29.096​

26.312​

10.721​

80​

0​

18​

0.747​

0.706​

0.940​

0.112​

0.859​

22.196​

20.984​

27.951​

3.333​

25.537​

85​

0​

19​

0.474​

0.825​

0.984​

0.824​

0.871​

11.921​

20.743​

24.738​

20.707​

21.891​

90​

0​

20​

0.952​

0.884​

0.258​

0.936​

0.050​

30.910​

28.698​

8.382​

30.399​

1.611​

95​

0​

21​

0.656​

0.584​

0.530​

0.097​

0.926​

23.480​

20.901​

18.973​

3.487​

33.159​

100​

0​

I can't rationalize that big 'knee.'

And here, 5000 numbers by string cutting:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

2​

0.00​

100.00​

38.52​

96.57​

54.47​

71.54​

38.52​

15.95​

17.07​

25.03​

3.43​

5​

924​

3​

0.00​

100.00​

35.25​

64.57​

84.72​

3.87​

3.87​

31.38​

29.32​

20.15​

15.28​

10​

782​

4​

0.00​

100.00​

71.59​

28.19​

37.45​

66.30​

28.19​

9.26​

28.84​

5.29​

28.41​

15​

644​

5​

0.00​

100.00​

45.27​

61.53​

39.99​

37.78​

37.78​

2.21​

5.28​

16.26​

38.47​

20​

579​

6​

0.00​

100.00​

98.21​

9.25​

64.24​

97.66​

9.25​

54.99​

33.42​

0.55​

1.79​

25​

488​

7​

0.00​

100.00​

31.31​

77.33​

91.52​

60.50​

31.31​

29.20​

16.82​

14.19​

8.48​

30​

419​

8​

0.00​

100.00​

27.76​

79.72​

38.63​

56.75​

27.76​

10.87​

18.12​

22.97​

20.28​

35​

307​

9​

0.00​

100.00​

34.39​

47.52​

82.44​

30.95​

30.95​

3.45​

13.13​

34.92​

17.56​

40​

218​

10​

0.00​

100.00​

56.12​

66.12​

42.23​

86.32​

42.23​

13.89​

10.00​

20.20​

13.68​

45​

189​

11​

0.00​

100.00​

76.69​

54.71​

66.73​

3.29​

3.29​

51.42​

12.03​

9.95​

23.31​

50​

139​

12​

0.00​

100.00​

40.84​

76.95​

13.31​

54.02​

13.31​

27.53​

13.18​

22.94​

23.05​

55​

102​

13​

0.00​

100.00​

66.17​

98.78​

94.57​

86.11​

66.17​

19.94​

8.46​

4.21​

1.22​

60​

81​

14​

0.00​

100.00​

80.69​

56.14​

30.44​

79.81​

30.44​

25.70​

23.67​

0.88​

19.31​

65​

49​

15​

0.00​

100.00​

48.19​

36.46​

0.96​

43.57​

0.96​

35.50​

7.11​

4.62​

51.81​

70​

40​

16​

0.00​

100.00​

37.83​

80.40​

72.51​

27.42​

27.42​

10.41​

34.68​

7.89​

19.60​

75​

22​

17​

0.00​

100.00​

65.29​

95.08​

78.59​

83.84​

65.29​

13.30​

5.25​

11.24​

4.92​

80​

11​

18​

0.00​

100.00​

46.02​

67.82​

34.79​

18.72​

18.72​

16.07​

11.23​

21.80​

32.18​

85​

6​

19​

0.00​

100.00​

23.55​

21.22​

71.55​

2.28​

2.28​

18.94​

2.32​

48.00​

28.45​

90​

0​

20​

0.00​

100.00​

54.98​

30.21​

88.31​

65.55​

30.21​

24.77​

10.57​

22.76​

11.69​

95​

0​

21​

0.00​

100.00​

44.00​

34.22​

20.66​

51.71​

20.66​

13.56​

9.79​

7.71​

48.29​

100​

0​

Again, I do agree that the method I suggested is inappropriate to modeling the age bands of a random sample of the population. I hope you'll agree that neither is yours

 

[shg]

This routine generates 5 random numbers between 0 and 100, and if their total is 100, lists them.

Sub x()
    Const m As Long = 101
    Dim n As Long
    Dim i As Long
    Dim i1 As Long
    Dim i2 As Long
    Dim i3 As Long
    Dim i4 As Long
    Dim i5 As Long
    
    Do
        i1 = Int(Rnd() * m)
        i2 = Int(Rnd() * m)
        i3 = Int(Rnd() * m)
        i4 = Int(Rnd() * m)
        i5 = Int(Rnd() * m)
        If i1 + i2 + i3 + i4 + i5 = 100 Then
            n = n + 1
            Rows(n).Range("A1:E1").Value = Array(i1, i2, i3, i5, i5)
        End If
        DoEvents
    Loop While n < 1000
End Sub

... it generates about the same distribution as string cutting.

 

[István Hirsch]

This routine generates 5 random numbers between 0 and 100, and if their total is 100, lists them.

Sub x()
    Const m As Long = 101
    Dim n As Long
    Dim i As Long
    Dim i1 As Long
    Dim i2 As Long
    Dim i3 As Long
    Dim i4 As Long
    Dim i5 As Long
    
    Do
        i1 = Int(Rnd() * m)
        i2 = Int(Rnd() * m)
        i3 = Int(Rnd() * m)
        i4 = Int(Rnd() * m)
        i5 = Int(Rnd() * m)
        If i1 + i2 + i3 + i4 + i5 = 100 Then
            n = n + 1
            Rows(n).Range("A1:E1").Value = Array(i1, i2, i3, i5, i5)
        End If
        DoEvents
    Loop While n < 1000
End Sub

... it generates about the same distribution as string cutting.

In the array i5 is twice, with i4 it works.

 

[shg]

Thank you, Istvan, I caught that earlier.

Then I wrote some nested loops that generates every combination of 5 numbers between 0 and 100 that totals 100 (there are 46,262, from {0,0,0,0,100} to {20,20,20,20,20}). The distribution of those numbers follows the same polynomial distribution as the string-cutting example. I would argue that any algorithm that purports to generates such samples should have approximately the same distribution.

 

[Ashwini Lambture]

Hi All, Thank You very much for solving my problem. I have 1 question on the same problem ,I forgot to mention it in the requirements, now I want random numbers to be generated in the 2nd row in such a way that the sum of those random numbers should be exact 100 and the random number which is generated in the column of Age(35-44) should be greater as compare to other columns of ages. And I should be able to apply the formula or whatever it is in next 100 rows.

 

[Kaper]

I would argue that any algorithm that purports to generates such samples should have approximately the same distribution.

May be not argue but some discussion in the morning is better than a cofee

If it would be the case of some lotto-type game or just pure statistical (by no means I am specialist in the field) discussion, I'd agree.
Well, may be it would be worth checking not just combinations but also take into account also sequence of numbers so somewhat more. Simple algorithm (4 loops) to write down all 4598126 (may be tested with mystep 5 or 2 first, but on my old computer it talkes less than a minute with mystep=1):

Sub test()
Dim t#, i&, j%, k%, l%, m%, n%, mystep%, outtable(1 To 1048576, 1 To 5) As Integer
Application.ScreenUpdating = False
t = Timer
mystep = 1
mycolumn = 1
For j = 0 To 100 Step mystep
 For k = 0 To 100 - j Step mystep
  For l = 0 To 100 - j - k Step mystep
   For m = 0 To 100 - j - k - l Step mystep
    n = 100 - j - k - l - m
        i = i + 1
        outtable(i, 1) = j
        outtable(i, 2) = k
        outtable(i, 3) = l
        outtable(i, 4) = m
        outtable(i, 5) = n
        If i >= 1048576 Then
          Cells(1, mycolumn).Resize(i, 5).Value = outtable
          i = 0
          mycolumn = mycolumn + 5
        End If
Next m, l, k, j
Cells(1, mycolumn).Resize(i, 5).Value = outtable
MsgBox Timer - t
End Sub

 

[Kaper]

Anyway. Going back to main idea - we impose a limit of specific sum on a distribution which would be otherwise flat - yes we can expect results similar to what you proposed.

But In the thread we have "real life case". I'd expect that, to better mimmick what could be observed in real sample from living population, we shall look for some distribution where for each age group either is rather flat distribution or there is a well defined modal value and this value is (at least in some groups) not 0.

So for instance to simulate somewhat, we would expect in real data sample I could propose such procedure which generates "close to normal" distribution for each age group:

Sub prepare()
Const expsum = 100
Dim expvals(1 To 5) As Double, stddevs(1 To 5) As Double, genvals(1 To 5) As Double
Dim i As Long, j As Integer, repcount As Integer, workingsum As Double, validsim As Boolean
'parameters. good idea to have sum of expvals(j)= expsum, smaller stdddevs -> narrower distrib.
expvals(1) = 20: expvals(2) = 20: expvals(3) = 30: expvals(4) = 20: expvals(5) = 10
stddevs(1) = 7: stddevs(2) = 7: stddevs(3) = 10: stddevs(4) = 7: stddevs(5) = 3
Randomize
For i = 2 To 101
  repcount = 0
  Do
    validsim = True: workingsum = 0
    For j = 1 To 5
      genvals(j) = Application.WorksheetFunction.NormInv(Rnd, expvals(j), stddevs(j))
      workingsum = workingsum + genvals(j)
      If genvals(j) < 0 Or genvals(j) > 2 * expvals(j) Then validsim = False
    Next j
    repcount = repcount + 1
  Loop Until validsim Or repcount > 1000
  If repcount > 1000 Then
    MsgBox "Consider changing simulation parameters!", vbCritical
    Exit Sub
  End If
  For j = 1 To 5
    Cells(i, j) = genvals(j) * expsum / workingsum
  Next j
Next i
End Sub

This code follows also the idea behind Ashwini last request to "control" how generated numbers are distributed amongst age groups. Althought not guaranted that always Age 35-44 would have greatest value, because again, I do not think it reflects well reality behind - there coukld be tendency that this group has highest value. As a matter of fact, for such parameters as in the code above one could expect some ca. 2/3 of cases where this age group will have greatest value.

 

[Kaper]

Hm, third post in a row. Hope moderators are tolerant on this forum

Returning to simple formulas from post #2 on first page and to exactly follow Ashwini's last request. In O1:

=IF(COUNTIF($J1:J1,MAX($J1:$N1))=1,K1,J1)

copy to P1. In Q1 just:

=MAX($J1:$N1)

in R1 (and copy to S1):

=IF(COUNTIF(M1:$N1,MAX($J1:$N1))=1,L1,M1)

copy all J1:S1 down as many rows as needed

in A2 minor change and also copy right and down:

=[COLOR=#ff0000]O[/COLOR]1*100/SUM($[COLOR=#ff0000]O[/COLOR]1:$[COLOR=#ff0000]S[/COLOR]1)

But ... I really liked the code in previous post and distribution it generates - use FREQUENCY array formula to have a look on them.

 

[shg]

Simple algorithm (4 loops) to write down all 4598126

Mine was similar, but I did only combinations:

    For i1 = 0 To m
        For i2 = i1 To m
            If i1 + i2 > m Then Exit For
            For i3 = i2 To m
                If i1 + i2 + i3 > m Then Exit For
                For i4 = i3 To m
                    i5 = m - (i1 + i2 + i3 + i4)
                    If i5 < i4 Then Exit For
                    n = n + 1
                    aiOut(n, 1) = i1
                    aiOut(n, 2) = i2
                    aiOut(n, 3) = i3
                    aiOut(n, 4) = i4
                    aiOut(n, 5) = i5
                    If (n And &HFFF&) = 0 Then Debug.Print n; i1; i2; i3; i4; i5
                Next i4
            Next i3
        Next i2
    Next i1
    
    Columns("A:E").ClearContents
    Range("A1:E1").Resize(n).Value = aiOut

Pondering that, looking at only combinations affects the distribution; {0,0,0,0,100} would appear in five permutations, where {0,1,2,3,94} would appear in 120.

 

[shg]

Here's the function I use for mulkti-ticket raffles:

Function RandDraws(nDraw As Long, avInp As Variant) As Long()
    ' shg 2014

    ' Returns the results of nDraw raffle-like drawings with the probability of
    ' win in each draw specified by avInp. For example, in B3:E3 and copied down,

    ' =RandDraws(A3, $B$1:$E$1)

    '      --A-- -B- -C- -D- E-
    '  1   Prob: 11% 76% 10% 3%
    '  2   Total  1   2   3  4
    '  3     100 12  73  12  3
    '  4     100  5  77  15  3
    '  5     100 15  73   7  5
    '  6     100  6  80  10  4
    '  7     100  8  78  10  4

    ' avInp is normalized to probabilities

    ' This could be modified to remove winning tickets at each draw.

    Dim adPrb()     As Double
    Dim adLUT()     As Double
    Dim aiOut()     As Long

    Dim i           As Long
    Dim n           As Long
    Dim dSum        As Double
    Dim dPrb        As Double

    Dim iDraw       As Long
    Dim iWin        As Long

    adPrb = Make1D(avInp, 1)

    n = UBound(adPrb)
    ReDim adLUT(1 To n)
    ReDim aiOut(1 To n)

    With WorksheetFunction
        dSum = .Sum(adPrb)

        For i = 1 To n
            adLUT(i) = dPrb
            dPrb = dPrb + adPrb(i) / dSum
        Next i

        Randomize

        For iDraw = 1 To nDraw
            iWin = .Match(CDbl(Rnd), adLUT)
            aiOut(iWin) = aiOut(iWin) + 1
        Next iDraw
    End With

    RandDraws = aiOut
End Function