Loop through and SUM total the ODD & EVEN Numbers.

[S.H.A.D.O.]

Good evening,

I would like to cycle through all the combinations and count the sum of the ODD numbers AND the sum of the EVEN numbers within each of the combination please.

I know the following:-
The LOWEST sum of ODD will be 0.
The LOWEST sum of EVEN will be 0.
The HIGHEST sum of ODD will be 264.
The HIGHEST sum of EVEN will be 258.

So, for example:-

06 24 30 39 46 49 = Odd 88 & Even 106
02 16 25 32 45 48 = Odd 70 & Even 98
03 20 27 29 36 47 = Odd 106 & Even 56
07 14 22 23 31 44 = Odd 61 & Even 80

So I will end up with the numbers from 0 to 264 in column A, the total for each of the sums of ODD numbers in column B and the total for each of the sums of EVEN numbers in column C.

Here is what I have so far but now I am stuck:-

Option Explicit
Option Base 1

Const MinA As Integer = 1
Const MaxF As Integer = 49

Sub Sum_Of_ODD_And_EVEN_Numbers()
    Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
    Dim i As Integer
    Dim nType(0 To 264) As Double
    Dim sum As Long
    Dim results(49) As Long
    Dim nTypeTotal As Long
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    With Sheets("Results").Select
        Range("A:C").ClearContents
        Range("A1").Select
        For i = LBound(nType) To UBound(nType)
            nType(i) = 0
        Next i
        For i = MinA To MaxF
            results(i) = i Mod 2
        Next i
        For A = MinA To MaxF - 5
            For B = A + 1 To MaxF - 4
                For C = B + 1 To MaxF - 3
                    For D = C + 1 To MaxF - 2
                        For E = D + 1 To MaxF - 1
                            For F = E + 1 To MaxF
                                sum = results(A) + results(B) + results(C) + results(D) + results(E) + results(F)
                                nType(sum) = nType(sum) + 1
                            Next F
                        Next E
                    Next D
                Next C
            Next B
        Next A
        nTypeTotal = ActiveCell.Row
        With ActiveCell
            For i = LBound(nType) To UBound(nType)
                .Offset(i - LBound(nType), 0).Value = i
                .Offset(i - LBound(nType), 1).Value = nType(i)
            Next i
            .Offset(i - LBound(nType), 1).FormulaR1C1 = "=Sum(R" & nTypeTotal & "C:R[-1]C)"
        End With
    End With
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks in advance.

 

[Trebor76]

Hi S.H.A.D.O.,

If the numbers in your first example are in A1:A6, simply put this formula into cell B1...

=SUMPRODUCT(--(MOD(A1:A6,2)=1),A1:A6)

...and this formula into cell C1:

=SUM(A1:A6)-SUMPRODUCT(--(MOD(A1:A6,2)=1),A1:A6)

So no need for a macro.

Regards,

Robert

 

[S.H.A.D.O.]

Thanks for the reply Trebor76,

Unfortunately this is not what I am after.
Those were just examples of achieving the totals for the ODD & EVEN numbers.
What I would like is for it to cycle through ALL C(49,6) combinations and keep a count of ALL the 264 different sum totals and then list them.

Thanks in advance.

 

[Trebor76]

What I would like is for it to cycle through ALL C(49,6) combinations and keep a count of ALL the 264 different sum totals and then list them.

Not sure if I'm understanding you correctly but there are nearly 14 million different combinations trying to pick 6 numbers from a total of 49 i.e. =COMBIN(49,6) So you would have to evaluate each result to see if met your criteria.

Excel will struggle trying to churn out that many combinations (at best) as there are "only" 1,048,576 rows per column and then to try and analyse them.

 

[S.H.A.D.O.]

Hi Robert,

I don't want ALL the combinations listed, I just want the 264 different total sums for both ODD & EVEN numbers listed please.
Thanks in advance.

 

[Trebor76]

How are you arriving at 264? From the nearly 14 million combinations there must be more than 264?

 

[S.H.A.D.O.]

Hi Robert,

For ODDS for example:-

The combination 02-04-06-08-10-12 will count as SUM Total = 0.
The combination 01-04-06-08-10-12 will count as SUM Total = 1.
The combination 01-03-05-06-08-10 will count as SUM Total = 9.
...
The combination 39-41-43-45-47-49 will count as SUM Total = 264.

The same logic also applies to the EVEN numbers.
Thanks in advance.

 

[Trebor76]

So like I (and you) said, you have to loop through each combination (14 million in this case) checking if each meets the criteria. Correct?

 

[S.H.A.D.O.]

Hi Robert,

That's correct, just loop through ALL the combinations and keep a count of the sum totals for ODD & EVEN numbers for each combination and list the totals for all the 264 categories.
Thanks for your time.

 

[Trebor76]

I may be wrong but I don't think Excel is the application for this as there will be more combinations than rows. It will also take a considerable amount of time to process that many records.

I'd suggest a database application solution such as Access or an SQL database.