Lottery Odds for 2 most frequent numbers out of 5 as well as the 3 most frequent numbers out of 5

[rebel123]

Enclosed are examples of the Florida Lotto Fantasy 5 which is 5 numbers
from 1 thru 36.

I am trying to figure out which 2 numbers appear TOGETHER the most frequently
as well as the 3 numbers that appear TOGETHER the most frequently.

The data set is.

1. All time numbers- 9260 results
2. The last 5 year numbers- 1461 results


I need = sign formulas for the areas in green.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are, without having to manually type it out. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here is one approach for the pairs.

Sub MostCommonPairs()
  Dim SL As Object, d As Object
  Dim a As Variant
  Dim i As Long, j As Long, uba2 As Long, x As Long, y As Long

  Set SL = CreateObject("System.Collections.Sortedlist")
  Set d = CreateObject("Scripting.Dictionary")
  a = Range("B2", Range("F" & Rows.Count).End(xlUp)).Value2
  uba2 = UBound(a, 2)
  For i = 1 To UBound(a)
    SL.Clear
    For j = 1 To uba2
      SL.Add a(i, j), a(i, j)
    Next j
    For x = 0 To uba2 - 2
      For y = x + 1 To uba2 - 1
        d(SL.GetByIndex(x) & ", " & SL.GetByIndex(y)) = d(SL.GetByIndex(x) & ", " & SL.GetByIndex(y)) + 1
      Next y
    Next x
  Next i
  SL.Clear
  For i = 1 To d.Count
      SL(d.Items()(i - 1)) = SL(d.Items()(i - 1)) & " | " & d.Keys()(i - 1)
  Next i
  Range("H2").Value = Mid(SL.GetByIndex(SL.Count - 1), 4)
  Range("I2").Value = d(Split(Range("H2").Value, " | ")(0)) & " of " & UBound(a)
End Sub

My sample data is in columns B:F and the above code produced the results in H2:I2

rebel123.xlsm
	B	C	D	E	F	G	H	I
1	Ball 1	Ball 2	Ball 3	Ball 4	Ball 5		Most Common Pairs	Frequency
2	26	9	25	10	12		28, 31 | 12, 30	4 of 29
3	11	22	24	29	20
4	32	13	17	23	14
5	31	5	2	7	28
6	12	30	20	27	29
7	7	35	32	5	10
8	31	4	26	34	21
9	5	29	18	7	36
10	16	21	22	12	15
11	2	16	29	23	30
12	31	32	7	25	19
13	21	8	29	34	25
14	21	24	33	20	1
15	1	16	34	12	30
16	14	11	24	12	30
17	8	10	20	17	30
18	23	29	6	5	19
19	2	4	3	6	20
20	20	12	3	15	30
21	31	28	8	11	35
22	15	16	4	30	1
23	28	1	4	25	2
24	19	13	7	30	35
25	25	10	14	23	18
26	25	14	21	30	36
27	36	13	9	24	30
28	28	29	20	18	5
29	28	31	14	34	8
30	36	31	28	32	9
31
Sheet1

 

[richh]

Hi Rebel,

Doesn't answer your question per se, but have you considered a heat amp? You can get yyour tep 5 or 10 list from there and visualize your "hottest" combinations.

In the data sheet, I split the balls into combinations

In the map sheet, I have 1-36 as row and col labels.

Formula for the array: =COUNTIF(Sheet2!$G$2:$J$10, $A2 & ";" & B$1)

Do note that these are one way matches (eg: 14;15 but not 15;14); It can be modified a bit to get two way matches (eg: 14:15 and 15:14)

Then, I applied a Top 10 Color set to the table.

 

[rebel123]

This is my Excel version.

 

[rebel123]

I do not know how to use VBA's which is why I was looking for an equals sign answer.

 

[rebel123]

I looked up XL2BB - Excel Range to BBCode but it does not make any sense- it is too technical for me.

I uploaded the file here-

https://easyupload.io/dhhxx2

 

[Peter_SSs]

I was looking for an equals sign answer.

I'm happy to be proven wrong, but I doubt you will get one which is why I suggested a vba approach.

I do not know how to use VBA's

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code provided into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the 'MostCommonPairs' macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[rebel123]

Thanks for both of your replies, Peter and Richh. I have ab iMac so there is no ALT F11. I also tried
command F11
Option F11
Control F11
FN F11
Shift F11

and no luck

 

[johnnyL]

The shortcut in the Mac version is Opt+F11 or Fn+Opt+F11.

 

[richh]

Only way I can think of getting the top combos is parsing out each ball combination possible, then counting how many times the ball combos are made using helper columns in the data source.

Data file with paring formula: =IF(A2<B2,A2&";"&B2,B2&";"&A2)

Then, the tally sheet using COUNTIF: =COUNTIF(Sheet2!$G$2:$J$50,Sheet1!A2)

Pivot table used for quick sorting to get top combos