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.
Function sbLongRandSumN(lSum As Long, _
ByVal lCount As Long, _
Optional ByVal lMin As Long = 0) As Variant
'Generates lCount random integers greater equal lMin
'which sum up to lSum.
'Reverse(moc.LiborPlus.www) V0.10 26-Apr-2013
Dim i As Long
Dim lSumRest As Long
If lCount * lMin > lSum Then
sbLongRandSumN = CVErr(xlErrNum)
Exit Function
End If
If lCount < 1 Then
sbLongRandSumN = CVErr(xlErrValue)
Exit Function
End If
ReDim vR(1 To lCount) As Variant
lSumRest = lSum
For i = lCount To 2 Step -1
vR(i) = lMin + Int(Rnd * (lSumRest - lMin * i))
lSumRest = lSumRest - vR(i)
Next i
vR(1) = lSumRest
sbLongRandSumN = vR
End Function
Copy link