Football result outcome.

[Davebro]

Hi, I am looking to find a formula to find the expected result comparing previous second half scores with the first half score
Which is the most likely result. e.g.

HT FT
0-0 0-0
0-0 3-1
0-0 0-0
0-0 0-0
0-0 2-2
0-0 2-0
0-0 0-1
0-0 1-0
0-0 1-1
0-0 0-0
0-0 4-0
0-0 0-2
0-0 2-2
0-0 2-1
0-0 2-1
0-0 2-1
0-0 1-0
0-0 1-1
0-0 0-2
0-0 0-0
0-0 1-0
0-0 2-1
0-0 1-0
0-0 0-0
0-0 0-2
0-0 1-0
0-0 1-3
0-0 2-1
0-0 0-0
0-0 0-0
0-0 0-1
0-0 0-1
0-0 0-1
0-0 0-2
0-0 0-1
0-0 0-0
0-0 2-2
0-0 0-0
0-0 2-0
0-0 1-0
0-0 1-1
0-0 1-1
0-0 1-3
0-0 2-3
0-0 0-0
0-0 4-0
0-0 1-1
0-0 3-2
0-0 2-1
0-0 4-2
0-0 3-2
0-0 1-1
0-0 1-1
0-0 2-1
0-0 1-0
0-0 4-0
0-0 0-1
0-0 2-1
0-0 2-1
0-0 0-0
0-0 0-1

 

[Marcelo Branco]

oops...

Array formula in E2 copied down
=IFERROR(INDEX(B$2:B$62,MODE(IF(A$2:A$62=D$2,IF(ISNA(MATCH(B$2:B$62,E$1:E1,0)),MATCH(B$2:B$62,B$2:B$62,0)+{0,0})))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Davebro]

oops...

Array formula in E2 copied down
=IFERROR(INDEX(B$2:B$62,MODE(IF(A$2:A$62=D$2,IF(ISNA(MATCH(B$2:B$62,E$1:E1,0)),MATCH(B$2:B$62,B$2:B$62,0)+{0,0})))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

This is just what I was after, this has a knock on effect to my other data, thanks so much for your expertise.

 

[Marcelo Branco]

This is just what I was after, this has a knock on effect to my other data, thanks so much for your expertise.

Small adjustment
Formula in F2 should be
=IF(E2="","",COUNTIFS(A$2:A$62,D$2,B$2:B$62,E2))
copied down

M.

 

[Davebro]

Just a small problem with duplicates
0-0 12
2-1 9
0-1 7
1-0 7
1-1 7

when ranking top 5 it shows
0-0 12
2-1 9
0-1 7
0-1 7
0-1 7

I wonder if there is a fix for duplicates?

 

[Marcelo Branco]

Try

FOOTBALL OUTCOME.xlsx
	A	B	C	D	E	F	G	H	I
1	HT	FT		Criteria (HT)	Results	Occurrences		5th Largest	Top 5
2	0-0	0-0		0-0	0-0	12		7	0-0
3	0-0	3-1			2-1	9			2-1
4	0-0	0-0			0-1	7			0-1
5	0-0	0-0			1-0	7			1-0
6	0-0	2-2			1-1	7			1-1
7	0-0	2-0			0-2	4
8	0-0	0-1			2-2	3
9	0-0	1-0			4-0	3
10	0-0	1-1			2-0	2
11	0-0	0-0			1-3	2
12	0-0	4-0			3-2	2
13	0-0	0-2			3-1	1
14	0-0	2-2			2-3	1
15	0-0	2-1			4-2	1
16	0-0	2-1
17	0-0	2-1
18	0-0	1-0
19	0-0	1-1
20	0-0	0-2
21	0-0	0-0
22	0-0	1-0
23	0-0	2-1
24	0-0	1-0
25	0-0	0-0
26	0-0	0-2
27	0-0	1-0
28	0-0	1-3
29	0-0	2-1
30	0-0	0-0
31	0-0	0-0
32	0-0	0-1
33	0-0	0-1
34	0-0	0-1
35	0-0	0-2
36	0-0	0-1
37	0-0	0-0
38	0-0	2-2
39	0-0	0-0
40	0-0	2-0
41	0-0	1-0
42	0-0	1-1
43	0-0	1-1
44	0-0	1-3
45	0-0	2-3
46	0-0	0-0
47	0-0	4-0
48	0-0	1-1
49	0-0	3-2
50	0-0	2-1
51	0-0	4-2
52	0-0	3-2
53	0-0	1-1
54	0-0	1-1
55	0-0	2-1
56	0-0	1-0
57	0-0	4-0
58	0-0	0-1
59	0-0	2-1
60	0-0	2-1
61	0-0	0-0
62	0-0	0-1
Plan2

Cell Formulas
Range	Formula
E2:E16	E2	=IFERROR(INDEX(B$2:B$62,MODE(IF(A$2:A$62=D$2,IF(ISNA(MATCH(B$2:B$62,E$1:E1,0)),MATCH(B$2:B$62,B$2:B$62,0)+{0,0})))),"")
F2:F16	F2	=IF(E2="","",COUNTIFS(A$2:A$62,D$2,B$2:B$62,E2))
H2	H2	=LARGE(F2:F15,5)
I2:I7	I2	=IF(F2>=H$2,INDEX(E$2:E$15,AGGREGATE(15,6,(ROW(E$2:E$15)-ROW(E$2)+1)/(F$2:F$15=F2),COUNTIF(F$2:F2,F2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

M.

 

[Davebro]

Whoa, that's fantastic, I have added all my result rows and changed the criteria to a drop down list so I am fully up and running, many thanks again for your help.

 

[Marcelo Branco]

You are welcome. Glad to help

M.