Sulprobil
Search…
sbLongRandSumN
You need to generate 20 non-negative random integers which sum up to 100? Then I suggest to use my UDF below. You can generate an arbitrary number of integers which sum up to a specified amount, and all generated integers are not smaller than another specified number:
Please notice that this function is a relative of sbRandIntFixSum (for which you need to provide non-optional minimum and maximum values to ge generated), the brownian bridge sbGrowthSeries, and of sbRandSum1 which generates numbers of type Double which sum up to one exactly. This function is used in my application sbGenerateTestData, for example.
Please read my Disclaimer.
1
Function sbLongRandSumN(lSum As Long, _
2
ByVal lCount As Long, _
3
Optional ByVal lMin As Long = 0) As Variant
4
'Generates lCount random integers greater equal lMin
5
'which sum up to lSum.
6
'Reverse(moc.LiborPlus.www) V0.10 26-Apr-2013
7
Dim i As Long
8
Dim lSumRest As Long
9
10
If lCount * lMin > lSum Then
11
sbLongRandSumN = CVErr(xlErrNum)
12
Exit Function
13
End If
14
If lCount < 1 Then
15
sbLongRandSumN = CVErr(xlErrValue)
16
Exit Function
17
End If
18
ReDim vR(1 To lCount) As Variant
19
lSumRest = lSum
20
For i = lCount To 2 Step -1
21
vR(i) = lMin + Int(Rnd * (lSumRest - lMin * i))
22
lSumRest = lSumRest - vR(i)
23
Next i
24
vR(1) = lSumRest
25
sbLongRandSumN = vR
26
End Function
Copied!
Last modified 1yr ago
Copy link