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.
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): http://www.sulprobil.com/sblongrandintsumn_en/ 'Source (DE): http://www.bplumhoff.de/sblongrandintsumn_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 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