Excel Formula- Combinations

[SHT]

Excel Experts,

I was wondering if it would be ok to request an excel formula.

Basically I have a spreadsheet of past lottery results and I require an excel formula to enable me to determine the frequency of repeating pair combinations in my lottery data. Would anyone be able to help me?

The lottery has 49 balls and 6 balls and the bonus ball are drawn. I have a lottery table with all lottery results in ( Columns B:H ).

COLUMNS

A ________B______C ____etc....

date&____1st No__2nd No__etc....
draw no 500

Thanks in advance

SHT.

 

[Irvin]

Hello SHT,

Try this;

Put this formula into cell M2

=N(AND((COUNTIF(b2:h2,$J$2)),(COUNTIF(b2:h2,$k$2))))

In the cell J2 and K2 put the 2 numbers you want to count.

Copy the formula from M2 all the way down to M501 if it is 500 draws.

This formula is based on 7 numbers per line ie 6+bonus. If you just want the 6 drawn numbers replace "h2" with "g2" in the formula.

in cell L2 put the formula =countif(M:M,1)

This way is a bit slow since there are 1176 pairs in 6/49 lotto.

The only cells you need to change after this are J2 and K2.

If GillesD can do a macro for this it would save you a lot of time. Just something to loop through a similar formula to the above 1176 times.

Regards,

Irvin

 

[GillesD]

Pairs of numbers

The following macro gives all the number of occurences of all 1176 pairs of numbers both when considering the 6 main numbers as well as including the bonus number for all draws of a 6/49 lottery.

On page named "Data", in cells A1 to H1 enter titles for Draws number, N1 to N6 and Bonus. In cell K1, enter the number of draws or the formula =MAX(A:A). From line 2, enter the data ine the following order in columns A to H: draw number, N1, N2, N3, N4. N5, N6 and Bonus number.

On a page named "Result", enter titles in A1 to Di; these could be V1 and V2 for value #1 and #2, Freq 6n and Freq 7n for frequency for 6 and 7 numbers.

Enter the following macro in a module and run it to get all occurences of 1176 pairs (1,2 to 48,49) for both 6 and 7 numbers of the lottery. Speed is function of your computer.

Option Explicit
Dim N1 As Integer, N2 As Integer, nCoupleA(49, 49) As Integer, nCoupleB(49, 49) As Integer
Dim nVal As Integer, I As Integer, J As Integer, nNb(7) As Integer, nTmp As Integer

Sub Pair()
Application.ScreenUpdating = False
Sheets("Data").Select
nVal = Range("K1").Value
For N1 = 1 To 49
For N2 = 1 To 49
nCoupleA(N1, N2) = 0
nCoupleB(N1, N2) = 0
Next N2
Next N1
Range("A1").Select
For I = 1 To nVal
ActiveCell.Offset(1, 0).Select
For J = 1 To 7
nNb(J) = ActiveCell.Offset(0, J).Value
Next J
nCoupleA(nNb(1), nNb(2)) = nCoupleA(nNb(1), nNb(2)) + 1
nCoupleA(nNb(1), nNb(3)) = nCoupleA(nNb(1), nNb(3)) + 1
nCoupleA(nNb(1), nNb(4)) = nCoupleA(nNb(1), nNb(4)) + 1
nCoupleA(nNb(1), nNb(5)) = nCoupleA(nNb(1), nNb(5)) + 1
nCoupleA(nNb(1), nNb(6)) = nCoupleA(nNb(1), nNb(6)) + 1
nCoupleA(nNb(2), nNb(3)) = nCoupleA(nNb(2), nNb(3)) + 1
nCoupleA(nNb(2), nNb(4)) = nCoupleA(nNb(2), nNb(4)) + 1
nCoupleA(nNb(2), nNb(5)) = nCoupleA(nNb(2), nNb(5)) + 1
nCoupleA(nNb(2), nNb(6)) = nCoupleA(nNb(2), nNb(6)) + 1
nCoupleA(nNb(3), nNb(4)) = nCoupleA(nNb(3), nNb(4)) + 1
nCoupleA(nNb(3), nNb(5)) = nCoupleA(nNb(3), nNb(5)) + 1
nCoupleA(nNb(3), nNb(6)) = nCoupleA(nNb(3), nNb(6)) + 1
nCoupleA(nNb(4), nNb(5)) = nCoupleA(nNb(4), nNb(5)) + 1
nCoupleA(nNb(4), nNb(6)) = nCoupleA(nNb(4), nNb(6)) + 1
nCoupleA(nNb(5), nNb(6)) = nCoupleA(nNb(5), nNb(6)) + 1
Select Case nNb(7)
Case Is < nNb(1)
nCoupleB(nNb(7), nNb(1)) = nCoupleB(nNb(7), nNb(1)) + 1
nCoupleB(nNb(7), nNb(2)) = nCoupleB(nNb(7), nNb(2)) + 1
nCoupleB(nNb(7), nNb(3)) = nCoupleB(nNb(7), nNb(3)) + 1
nCoupleB(nNb(7), nNb(4)) = nCoupleB(nNb(7), nNb(4)) + 1
nCoupleB(nNb(7), nNb(5)) = nCoupleB(nNb(7), nNb(5)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is < nNb(2)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(7), nNb(2)) = nCoupleB(nNb(7), nNb(2)) + 1
nCoupleB(nNb(7), nNb(3)) = nCoupleB(nNb(7), nNb(3)) + 1
nCoupleB(nNb(7), nNb(4)) = nCoupleB(nNb(7), nNb(4)) + 1
nCoupleB(nNb(7), nNb(5)) = nCoupleB(nNb(7), nNb(5)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is < nNb(3)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(2), nNb(7)) = nCoupleB(nNb(2), nNb(7)) + 1
nCoupleB(nNb(7), nNb(3)) = nCoupleB(nNb(7), nNb(3)) + 1
nCoupleB(nNb(7), nNb(4)) = nCoupleB(nNb(7), nNb(4)) + 1
nCoupleB(nNb(7), nNb(5)) = nCoupleB(nNb(7), nNb(5)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is < nNb(4)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(2), nNb(7)) = nCoupleB(nNb(2), nNb(7)) + 1
nCoupleB(nNb(3), nNb(7)) = nCoupleB(nNb(3), nNb(7)) + 1
nCoupleB(nNb(7), nNb(4)) = nCoupleB(nNb(7), nNb(4)) + 1
nCoupleB(nNb(7), nNb(5)) = nCoupleB(nNb(7), nNb(5)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is < nNb(5)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(2), nNb(7)) = nCoupleB(nNb(2), nNb(7)) + 1
nCoupleB(nNb(3), nNb(7)) = nCoupleB(nNb(3), nNb(7)) + 1
nCoupleB(nNb(4), nNb(7)) = nCoupleB(nNb(4), nNb(7)) + 1
nCoupleB(nNb(7), nNb(5)) = nCoupleB(nNb(7), nNb(5)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is < nNb(6)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(2), nNb(7)) = nCoupleB(nNb(2), nNb(7)) + 1
nCoupleB(nNb(3), nNb(7)) = nCoupleB(nNb(3), nNb(7)) + 1
nCoupleB(nNb(4), nNb(7)) = nCoupleB(nNb(4), nNb(7)) + 1
nCoupleB(nNb(5), nNb(7)) = nCoupleB(nNb(5), nNb(7)) + 1
nCoupleB(nNb(7), nNb(6)) = nCoupleB(nNb(7), nNb(6)) + 1
Case Is > nNb(6)
nCoupleB(nNb(1), nNb(7)) = nCoupleB(nNb(1), nNb(7)) + 1
nCoupleB(nNb(2), nNb(7)) = nCoupleB(nNb(2), nNb(7)) + 1
nCoupleB(nNb(3), nNb(7)) = nCoupleB(nNb(3), nNb(7)) + 1
nCoupleB(nNb(4), nNb(7)) = nCoupleB(nNb(4), nNb(7)) + 1
nCoupleB(nNb(5), nNb(7)) = nCoupleB(nNb(5), nNb(7)) + 1
nCoupleB(nNb(6), nNb(7)) = nCoupleB(nNb(6), nNb(7)) + 1
End Select
Next I
Range("A1").Select
Sheets("Result").Select
Range("A1").Select
For I = 1 To 48
For J = I + 1 To 49
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = I
ActiveCell.Offset(0, 1).Value = J
ActiveCell.Offset(0, 2).Value = nCoupleA(I, J)
ActiveCell.Offset(0, 3).Value = nCoupleA(I, J) + nCoupleB(I, J)
Next J
Next I
Application.ScreenUpdating = True
Sheets("Result").Select
Range("A1").Select
End Sub

 

[Irvin]

GillesD

Look'n good.......Dont look a day over 21 .

Late Happy birthday and thanks for coming up with the macro.

Hope you had a great long birthday Weekend.

Regards Irvin.

Sixth sense: "I see nice people"

 

[SHT]

Your responses were highly appreciated.

Thanks Giles for the Macro and detailed instructions, it was ideally suited to my needs.


SHT

 

[SHT]

Message for Giles

I hope you don't mind me asking Giles, but I am trying to determine the frequency of repeating triples in my original lottery data. I was wondering if it would be ok to ask you, if you have a macro that would enable me to achieve my aims?

My feeble attempts to modify your original macro was a total disaster.

The worksheet is arranged as you previously instructed me. But the analysis results for triple combinations is to be placed on a page named "Result2".

SHT.

 

[SHT]

I've reposted the above message in a new thread.

SHT

 

[PAB]

Hi GillesD,

Out of Interest, what does the "Dim nTmp As Integer" do.
The Program works great though, and it is very quick.

Best Refards
PAB

 

[GillesD]

VBA explanation

The statement "Dim nTmp As Integer" just declares the variable nTmp as being of an integer type which will accept values between -32,768 and +32,767.

 

[PAB]

Thanks very much for the Explanation GillesD.

All the Best.
PAB

 

[win2012]

excel

Would you send me excel sheet for 6/49 by email <<< email addy deleted - please don't post contact information >>> ?

 

[PAB]

Hi win2012,

Welcome to the board.
In the RULES it says that members must NOT post their email address.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[LT]

Thanks very much PAB