VBA code to list and calculate all possible combination 25C5 (i.e. in total 53130 combinations) of the mean of all 5 prices in a separate column

[dhubenov1]

Hello,

could you please help me with the VBA code as I am trying to get a separate column calculating the mean of all the possible combinations of 25C5 (i.e. 53130 combinations in total).

Here is the code I am using but I do not know what to do to get my results? D column are my 25 prices that I want to calculate the mean of each 5 prices of all the possible combinations out of the set of 25 prices.

Sub ListThemAll()
TC = 1
TR = 1
Ctr = 1
MaxRows = Rows.Count
EndCell = 53130
Application.ScreenUpdating = False
For a = 1 To 25
For b = (a + 1) To 22
For c = (b + 1) To 23
For d = (c + 1) To 24
For e = (d + 1) To 25
Application.StatusBar = Ctr & " on way to " & EndCell
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
Cells(TR - 20, TC).Select
Application.ScreenUpdating = True
ThisWorkbook.Save
Application.ScreenUpdating = False
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e
Next d
Next c
Next b
Next a
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

This is what I am trying to achieve on the attached file and I am at 1715 combination but manually it will take me a long time.

Attachments​

• 
  Capture.PNG
  44.1 KB · Views: 3

 

[JGordon11]

The mean of all the sums of 53130 groups of five prices out of 25 is the same as the average of the 25 prices multiplied by 5. But if you want excel to do 53130 sums and then average them here's a function that will do that for the data formatted as in your example:

Function MeanSum25Choose5(ComboRange, DataRange)
    Dim cr, dr, combo, c, i As Long, subtotal As Double, total As Double
    cr = ComboRange
    dr = DataRange
    For Each combo In cr
        c = Split(combo, "-")
        subtotal = 0
        For i = 0 To 4
            subtotal = subtotal + dr(c(i), 1)
        Next
        total = total + subtotal
    Next
    MeanSum25Choose5 = total / 53130
End Function

Note column A data extends to 53130 rows:

Book2
	A	B	C	D	E	F	G
1	1-2-3-4-5			80		284.2
2	1-2-3-4-6			35		284.2
3	1-2-3-4-7			59
4	1-2-3-4-8			47
5	1-2-3-4-9			38
6	1-2-3-4-10			23
7	1-2-3-4-11			78
8	1-2-3-4-12			70
9	1-2-3-4-13			63
10	1-2-3-4-14			72
11	1-2-3-4-15			29
12	1-2-3-4-16			43
13	1-2-3-4-17			65
14	1-2-3-4-18			65
15	1-2-3-4-19			74
16	1-2-3-4-20			67
17	1-2-3-4-21			69
18	1-2-3-4-22			76
19	1-2-3-4-23			25
20	1-2-3-4-24			65
21	1-2-3-4-25			75
22	1-2-3-5-6			26
23	1-2-3-5-7			54
24	1-2-3-5-8			61
25	1-2-3-5-9			62
26	1-2-3-5-10
27	1-2-3-5-11
28	1-2-3-5-12
Sheet3

Cell Formulas
Range	Formula
F1	F1	=MeanSum25Choose5(A1:A53130,D1:D25)
F2	F2	=AVERAGE(D1:D25)*5

 

[dhubenov1]

Hi JGordon11,

Thank you for your response and help.

I would appreciate your help if you could have the mean results ( i.e. 1st combination using your suggested numbers from the attached pic is (1+2+3+4+5)/5 = (80+35+59+47+38)/5 = 51.8 and the 2nd result would be 1+2+3+4+6/5= 80+35+59+47+23/5 = 48.8 and so on for each of these 53130 combinations separately)......

What your suggested formula does is something different based on the result from your numbers as the mean from the 1st combination should be 51.8 and from the second 48.8 and so on where in your example the results are 284.2 which is not what i am looking at.....

many thanks in advance for your help......

I am attaching a pic for more clarity as I need (Price from column D1+Price D2+ Price D3 + Price D4 + Price D5) /5 and so on the calculations for all the different combinations as i am comparing these resuls with another price for a research.....

 

[johnnyL]

@dhubenov1 add the following code to the end of your code in post #1:

Dim ArrayRow    As Long
Dim InputArray  As Variant
Dim OutputArray As Variant
Dim SplitCombo  As Variant
'
InputArray = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim OutputArray(1 To UBound(InputArray, 1), 1 To 1)
'
For ArrayRow = 1 To UBound(InputArray, 1)
    SplitCombo = Split(InputArray(ArrayRow, 1), "-")
    OutputArray(ArrayRow, 1) = "=(D" & SplitCombo(0) & "+D" & SplitCombo(1) & "+D" & SplitCombo(2) & "+D" & SplitCombo(3) & "+D" & SplitCombo(4) & ")/5"
Next
'
Range("B1").Resize(UBound(OutputArray, 1)) = OutputArray

 

[dhubenov1]

@dhubenov1 add the following code to the end of your code in post #1:

Dim ArrayRow    As Long
Dim InputArray  As Variant
Dim OutputArray As Variant
Dim SplitCombo  As Variant
'
InputArray = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim OutputArray(1 To UBound(InputArray, 1), 1 To 1)
'
For ArrayRow = 1 To UBound(InputArray, 1)
    SplitCombo = Split(InputArray(ArrayRow, 1), "-")
    OutputArray(ArrayRow, 1) = "=(D" & SplitCombo(0) & "+D" & SplitCombo(1) & "+D" & SplitCombo(2) & "+D" & SplitCombo(3) & "+D" & SplitCombo(4) & ")/5"
Next
'
Range("B1").Resize(UBound(OutputArray, 1)) = OutputArray

Hi JohnnyL,

magnificient!
Thank you very much indeed for your priceless help!
Have a great evening......

 

[johnnyL]

Glad to help.