Combinations summing up to 100%

[Divius]

Could someone explain me how I can get in excel all the combinations of 3 variables with in an interval of 1%.
For example when there are 2 variables there are 11 combinations with an interval of 10%.

1.0 0.9 0.8 0.7 0.6 0.5 0.4 0.3 0.2 0.1 0
0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0

Thanx in advance!

[shg]

This will list the 5151 combinations. It uses whole numbers rather than decimals. but you can divide all the numbers by 100.

Sub testnComb()
    Debug.Print nComb(3, 100)
End Sub

Function nComb(nItems As Long, nTot As Long) As Long
    ' Returns the number of combinations that nItems can be adjusted
    ' in increments of 1 such that the total is always nTot

    Dim ai()    As Long
    Dim i       As Long
    Dim iSum    As Long

    ReDim ai(1 To nItems)

    ' setup to get first combination and ensure initial correct total
    ai(1) = -1
    ai(nItems) = nTot
    iSum = nTot - 1

    Do
        iSum = iSum - ai(nItems)
        ai(nItems) = 0
        For i = 1 To nItems - 1
            If iSum < nTot Then
                iSum = iSum + 1
                ai(i) = ai(i) + 1
                Exit For
            Else
                iSum = iSum - ai(i)
                ai(i) = 0
            End If
        Next i
        If i > nItems - 1 Then Exit Function    '-------------------------->
        ai(nItems) = nTot - iSum
        iSum = nTot
        nComb = nComb + 1
        '*******************************
        Dim iRow As Long
        iRow = iRow + 1
        Cells(iRow, 1).Resize(, nItems).Value = ai
        '*******************************
    Loop
End Function

[Divius]

Thank you very much for providing me the code!

[shg]

You're welcome