Abstract

Suppose you have a row with 9 numbers. The average is always calculated as an integer (rounded down). Can you list all possible combinations of minimal value increases to reach the next higher average, if you are only allowed to increase numbers up to the current average?

Example

Combinations_to_increase_average

Appendix Sub Combinations Code

Please read my Disclaimer.

Options Explicit

Sub Combinations()
'Source (EN): http://www.sulprobil.com/combinations_which_increase_average_en/
'Source (DE): http://www.bplumhoff.de/combinations_which_increase_average_de/
'(C) (P) by Bernd Plumhoff 29-Jun-2020 PB V0.1
Dim i As Long, j As Long
Dim lCount As Long, lSumTarget As Long, lAvg As Long
Dim dAvg As Double
Dim v As Variant, vMax As Variant, vMin As Variant

With Application.WorksheetFunction

j = 10
v = Range(Cells(2, 1), Cells(2, 1).End(xlToRight))
lCount = UBound(v, 2) - LBound(v, 2) + 1
dAvg = .Average(v)
lAvg = .RoundDown(dAvg, 0)
lSumTarget = .RoundDown(dAvg + 1#, 0) * lCount

vMax = v
For i = 1 To lCount
    If vMax(1, i) < lAvg Then vMax(1, i) = lAvg
Next i

vMin = v
Range("10:65536").Delete
Select Case .Sum(vMax)
Case Is < lSumTarget
    [A10] = "There is no solution."
Case Is = lSumTarget
    Range(Cells(j, 1), Cells(j, lCount)).FormulaArray = vMax
Case Else
    i = 1
    Do While i <= lCount
        Do While v(1, i) = vMax(1, i)
            i = i + 1
            If i > lCount Then Exit Sub
        Loop
        v(1, i) = v(1, i) + 1
        Do While i > 1
            i = i - 1
            v(1, i) = vMin(1, i)
        Loop
        If .Sum(v) = lSumTarget Then
            Range(Cells(j, 1), Cells(j, lCount)).FormulaArray = v
            j = j + 1
        End If
    Loop
End Select

End With

End Sub