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:
The sum of input row numbers (row 2) is 42. You need a sum of 45 to get to the next higher average of 5. Since you can only increase numbers up to the current average of 4, you can only change input cells E2 and I2. There are three possible combinations which are listed in rows 10:12. This problem has been stated in a German Excel forum.
Please read my Disclaimer.
Sub Combinations()'https://berndplumhoff.gitbook.io/sulprobil/excel/excel-vba-solutions/combinatorial-fun/list-all-combinations-which-increase-average V0.1 29-Jun-2020Dim i As Long, j As LongDim lCount As Long, lSumTarget As Long, lAvg As LongDim dAvg As DoubleDim v As Variant, vMax As Variant, vMin As Variant​With Application.WorksheetFunction​j = 10v = Range(Cells(2, 1), Cells(2, 1).End(xlToRight))lCount = UBound(v, 2) - LBound(v, 2) + 1dAvg = .Average(v)lAvg = .RoundDown(dAvg, 0)lSumTarget = .RoundDown(dAvg + 1#, 0) * lCount​vMax = vFor i = 1 To lCountIf vMax(1, i) < lAvg Then vMax(1, i) = lAvgNext i​vMin = vRange("10:65536").DeleteSelect Case .Sum(vMax)Case Is < lSumTarget[A10] = "There is no solution."Case Is = lSumTargetRange(Cells(j, 1), Cells(j, lCount)).FormulaArray = vMaxCase Elsei = 1Do While i <= lCountDo While v(1, i) = vMax(1, i)i = i + 1If i > lCount Then Exit SubLoopv(1, i) = v(1, i) + 1Do While i > 1i = i - 1v(1, i) = vMin(1, i)LoopIf .Sum(v) = lSumTarget ThenRange(Cells(j, 1), Cells(j, lCount)).FormulaArray = vj = j + 1End IfLoopEnd Select​End With​End Sub