Run "x" number of random draws and calculate totals against a pre-defined set.

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

Good afternoon,

What I basically want to do it to run a certain number of random combinations as input by the user via an input box.
The combinations are in the format of 6 numbers plus an extra bonus number without repitition from a pool of 59 numbers.
I would like the program to pick up the 6 numbers to check against from cells C4:H4 within the Worksheet.
I would then like the program to calculate the total matches for each random combination that matched the 6 number combination in cells C4:H4 in the format:

0 Matches
0 Matches + Extra Bonus Number
1 Matches
1 Matches + Extra Bonus Number
2 Matches
2 Matches + Extra Bonus Number
3 Matches
3 Matches + Extra Bonus Number
4 Matches
4 Matches + Extra Bonus Number
5 Matches
5 Matches + Extra Bonus Number
6 Matches

So for each random combination it will calculate the criteria above and at the end it will add all of same criteria together to give one line of results.
This will produce a single row of totals for each of the criteria above to go into cells C7:O7.
I think it could possibly involve If...Then...Else If (several of)...Else...End If, but I don't know how to go about this or how to get this to work.

It is basically a way of running "x" number of random draws against a pre-defined set of 6 numbers and producing the overall results for each of the criteria stated in a single row from cells C7:O7.

I hope I have explained this clearly enough.

Thanks in advance.

 

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

Good afternoon,

I have put the following together from a couple of other programs I have to generate random numbers.

This generates 7 random numbers (6 main numbers + the bonus number) and outputs them to the Worksheet which is not what I want.

The 7 generated random numbers works well and there is no repetition within any of the combinations, but it sorts them in ascending order from left to right which is not what I want, they need to be in the order generated.

Is there any way to adapt this code so they are not sorted from left to right and are not output to the Worksheet.

Instead, each random combination generated is compared to the combination in cells C4:H4 and a count is done for each of the criteria stated in my original post.

Once this is done the totals for each of the criteria for each of the random combinations generated are added together to produce a single row of totals to be output to cells C7:O7.

Option Explicit
Option Base 1

Const m As Long = 7 ''' Numbers drawn (6 main plus extra bonus number).

Sub Generate_Random_A()
    Dim a(), b() As Boolean
    Dim i As Long, k As Long, x As Long
    Dim n As Integer ''' Max number drawn from.
    Dim RandCombs As Long ''' Number of random combinations.
    Dim rws As Long
    Dim Response
    n = Application.InputBox( _
        "How many numbers to be drawn from?", "Parameter - ""n""", 0)
        If n = 0 Then Exit Sub
    RandCombs = Application.InputBox( _
        "How many random combinations?", "Random Combinations", 0)
        If RandCombs = 0 Then Exit Sub
    With Application
        .ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
    End With
    ReDim a(1 To RandCombs, 1 To m)
    Randomize
    For i = 1 To RandCombs
        ReDim b(1 To n): k = 0
        Do
            x = Int(Rnd * n) + 1
            If Not b(x) Then k = k + 1: b(x) = True
        Loop Until k = m
        k = 0
        For x = 1 To n
            If b(x) Then k = k + 1: a(i, k) = x
        Next x
    Next i
    Range("D1").Resize(RandCombs, m) = a
    With Application
        .DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
    End With
End Sub

Thanks in advance.

 

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

Good evening,

I have been trying to incorporate the combination I want the user input 'x' random combinations calculated against.

I am not sure which of the following would be more suitable as a starting point of adapting the above code?

Either something like...

Dim MyArray(6) As Variant
MyArray = Range("C4:H4")

...or something like this...

Dim MyRng As Range
Set MyRng = Range("C4:H4")

Obviously I would then need a way of calculating all the criteria for all the user input random numbers generated.

As I said previously, I think it could possibly involve If...Then...Else If (several of)...Else...End If, but I don't know how to go about this in respect to incorporating it into the code.

Any help will be greatly appreciated.

Thanks in advance.

 

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

Good morning,

I think the code adaption for checking the set combination against all the random combinations generated will be along the lines of...

    Dim bBlank As Boolean
    Dim MyRng As Range
    Dim RandCombs As Range
    Dim Bonus As Long
    Dim NonBonus As Long
    Dim Matched() As Long

    Set MyRng = Range("C4:H4")

        For Each RandCombs In i
            Erase Matched: ReDim Matched(0 To 13)
            For Each MyRng In .Range("C4:H4")
                bBlank = (MyRng = 0): If bBlank Then GoTo Nextnum
                NonBonus = Evaluate("Sum(Countif(MyRng")
                Bonus = Evaluate("Countif(MyRng")
'               0 Matched
                If NonBonus = 0 And Bonus = 0 Then
                    Matched(1) = Matched(1) + 1
'               0 + Bonus Matched
                ElseIf NonBonus = 0 And Bonus = 1 Then
                    Matched(2) = Matched(2) + 1
'               1 Matched
                ElseIf NonBonus = 1 And Bonus = 0 Then
                    Matched(3) = Matched(3) + 1
'               1 + Bonus Matched
                ElseIf NonBonus = 1 And Bonus = 1 Then
                    Matched(4) = Matched(4) + 1
'               2 Matched
                ElseIf NonBonus = 2 And Bonus = 0 Then
                    Matched(5) = Matched(5) + 1
'               2 + Bonus Matched
                ElseIf NonBonus = 2 And Bonus = 1 Then
                    Matched(6) = Matched(6) + 1
'               3 Matched
                ElseIf NonBonus = 3 And Bonus = 0 Then
                    Matched(7) = Matched(7) + 1
'               3 + Bonus Matched
                ElseIf NonBonus = 3 And Bonus = 1 Then
                    Matched(8) = Matched(8) + 1
'               4 Matched
                ElseIf NonBonus = 4 And Bonus = 0 Then
                    Matched(9) = Matched(9) + 1
'               4 + Bonus Matched
                ElseIf NonBonus = 4 And Bonus = 1 Then
                    Matched(10) = Matched(10) + 1
'               5 Matched
                ElseIf NonBonus = 5 And Bonus = 0 Then
                    Matched(11) = Matched(11) + 1
'               5 + Bonus Matched
                ElseIf NonBonus = 5 And Bonus = 1 Then
                    Matched(12) = Matched(12) + 1
'               6 Matched
                Else
                    Matched(NonBonus) = Matched(NonBonus) + 1
                End If
Nextnum:
            Next MyRng
            RandCombs.Offset(3, 8).Resize(1, 13).Value = Matched
        Next RandCombs

Is this correct, and if so, how do I incorporate it into the code in post #2 for example please?

Any help will be greatly appreciated.

Thanks in advance.

 

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

Good afternoon,

If anyone has got any ideas on how this can be achieved I would appreciate it.

I have also just posted this request here:

Run "x" number of random draws and calculate totals against a pre-defined set.

Thanks in advance.

 

[Kenneth Hobson]

Sounds like you want permutations for powerball lottery? There are several Excel files that do that sort of thing I suspect.

For 7 ball permutations for 7 numbers, there are 5,040. So, you want 1 random set from that list of 5040? 6 permutations would be 720. Of course there are several examples that do permutations as well.

 

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

Good afternoon Kenneth and thank you very much for the reply.

I understand the difference between permutations and combinations but for what I am trying to achieve it definitely needs to be combinations because no number is repeated within each singular combination. It is basically combinations in a non sorted order which will give me the extra bonus ball in position 7.

I basically want to generate 'x' number of random combinations and for each of those combinations count the total number that relate to each of the scenarios I stated in post #1.

I then want to total all of the same scenarios together giving me a single total line with 13 totals.

If I was to use permutations instead there could possibly be many random combinations that contain all 6 numbers the same, just in a different order.

Thanks in advance.

 

[Kenneth Hobson]

Seems like I did something like this years ago but can't find that file right now.

When I help, I like to keep on track with what the op asked. I find that example files help me do that best. Plus, I am lazy but it does improve my solutions usually.

For this forum, try posting a link to a file that has the raw data for me or others to work with. I normally use dropbox.

As I said though, there are many files out there that may get close to what you want.

Some forums don't like gambling issues discussed for religious reasons I guess. Be sure to check forum rules before posts. Random is a mathematical deal though.

 

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

Thank again Kenneth for the reply.

I have put a small Excel file together to show exactly what I am after.

I have put 12 random combinations in cells D14:J25 and to the right of each I have calculated what each of the matches are with the combination set in cells C4:H4.

Obviously the random combinations will not be output to the Worksheet but will be run and totaled in memory and then the grand totals will be output to cells C8:O8.

I hope I have made this clearer.

Here is the link:
Run x number of random draws and calculate totals against a pre-defined set.

Thanks in advance.

 

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

I have had a good go at this but don't seem to be getting anywhere.

I know the code in post #2 works for producing random number combinations other than the fact that it sorts them in ascending order and writes them to the Spreadsheet.

I had a go in post #4 at what could possibly be a way of calculating the 13 different scenarios but have been unable to adapt it to work properly or indeed incorporate it into the code in post #2.

Any help will be appreciated.