Abstract

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:

sbLongRandSumN_Screen

Please notice that this function is a relative of sbRandIntFixSum (for which you need to provide non-optional minimum and maximum values to be 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.

Appendix sbLongRandSumN Code

Please read my Disclaimer.

Option Explicit

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.
'Source (EN): https://www.sulprobil.com/sblongrandsumn_en/
'Source (DE): https://www.bplumhoff.de/sblongrandsumn_de/
'(C) (P) by Bernd Plumhoff 26-Apr-2013 PB V0.1
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
Randomize
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

Sub Test_sbLongRandSumN()
Dim i As Long
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
ws.Range("A2:N102").ClearContents
For i = 2 To 101
    ws.Cells(i, 1) = Int(Rnd * 100 + 10)
    ws.Cells(i, 2) = Int(Rnd * 10 + 1)
    ws.Cells(i, 3) = Int(Rnd * ws.Cells(i, 1) / ws.Cells(i, 2))
    ws.Cells(i, 4).FormulaR1C1 = "=SUM(RC[1]:RC[" & ws.Cells(i, 2) & "])"
    Range(ws.Cells(i, 5), ws.Cells(i, ws.Cells(i, 2) + 4)).FormulaArray = _
        "=sbLongRandSumN(A" & i & ",B" & i & ",C" & i & ")"
Next i

End Sub

Please read my Disclaimer.

sbLongRandSumN.xlsm [25 KB Excel file, open and use at your own risk]