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:
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.de/sblongrandsumn_en/
'Source (DE): https://www.berndplumhoff.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]