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]

What exactly means 0-1 in HT column?
It means HT made 0 goals in the first half and 1 goal in the second?
The same for column FT?

M.

 

[Davebro]

What exactly means 0-1 in HT column?
It means HT made 0 goals in the first half and 1 goal in the second?
The same for column FT?

M.

Half Time HT, Full Time FT. In this case 0-0 at half time

 

[Marcelo Branco]

Considering data sample above could you please tell us the expected/desired results?

M.

 

[Davebro]

Considering data sample above could you please tell us the expected/desired results?

M.

I am trying to find which is the most likeliest out come, say is it 1-0, 2-1, 1-1 etc.

 

[Marcelo Branco]

Sorry, i'm not understanding what you are trying to do.
What is the logic to get "most likeliest outcome"?

M.

 

[Pumperkin]

Hi, Based upon your data it is 0-0. There are twelve instances of this score at FT (second place is 0-1 with seven instances).

First I split your data into columns for FT Home and FT Away, using MID(A3, 5, 1) and MID(A3, 7, 1) respectively. I then concatenated the two cells using (FTH+(FTA/10)) to give results 0, 3.1, 0, 0, 2.2 etc. I then just looked for the modal value in that column.

 

[Davebro]

Sorry, i'm not understanding what you are trying to do.
What is the logic to get "most likeliest outcome"?

M.

Hi, I have hundreds of scores for half time and full time based on a criteria, I am trying to find I suppose a rank for the full time scores again the half time score, does 1-1 occur more times than 1-0 or 2-1 etc.

 

[Pumperkin]

Hi, I have hundreds of scores for half time and full time based on a criteria, I am trying to find I suppose a rank for the full time scores again the half time score, does 1-1 occur more times than 1-0 or 2-1 etc.

Hi, Please note my answer is based upon the data given.

However, it should be fairly simple to filter the data from HT and use an IF formula to get the modal value (i.e, IF(HT=0, MODE(FT)). Then just modify the 0 to the possible outcomes (0.1, 0.2, 0.3 etc). If you write the outcomes in a list you can then just reference them rather than manually modifying the list.

If you can give the full data set I can give it a go

 

[Marcelo Branco]

Hi, I have hundreds of scores for half time and full time based on a criteria, I am trying to find I suppose a rank for the full time scores again the half time score, does 1-1 occur more times than 1-0 or 2-1 etc.

Maybe something like this

Pasta2
	A	B	C	D	E	F
1	HT	FT		Criteria (HT)	Results	Occurrences
2	0-0	0-0		0-0	0-0	12
3	0-0	3-1			2-1	9
4	0-0	0-0			0-1	7
5	0-0	0-0			1-0	7
6	0-0	2-2			1-1	7
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
ORIGINAL

Cell Formulas
Range	Formula
E2:F18,E19:E20	F2	=IF(E2="","",COUNTIF(B$2:B$62,E2))

Hope this helps

M.