Seeking advice on where to begin - Searching for team form football data

[widgewilliams]

Hi guys and gals.

At this stage Im not looking for a specific solution to a problem but rather ideas on how to approach something.

I have several spreadsheets of football data covering several seasons of results and odds.

eg:

Date	HomeTeam	AwayTeam	Home Goals	Away Goals	Result	Home Odds	Draw Odds	Away Odds	H	A
18/08/12	Arsenal	Sunderland	0	0	D	1.44	4.89	9.5	D	D
18/08/12	Fulham	Norwich	5	0	H	1.85	3.82	4.8	W	L
18/08/12	Newcastle	Tottenham	2	1	H	2.7	3.47	2.85	W	L
18/08/12	QPR	Swansea	0	5	A	2.1	3.55	4.2	L	W
18/08/12	Reading	Stoke	1	1	D	2.45	3.4	3.26	D	D
18/08/12	West Brom	Liverpool	3	0	H	4.38	3.61	2	W	L
18/08/12	West Ham	Aston Villa	1	0	H	2.3	3.46	3.51	W	L
19/08/12	Man City	Southampton	3	2	H	1.21	7.55	19.38	W	L
19/08/12	Wigan	Chelsea	0	2	A	6.75	4	1.67	L	W
20/08/12	Everton	Man United	1	0	H	4.45	3.71	1.91	W	L
22/08/12	Chelsea	Reading	4	2	H	1.29	6.2	14	W	L
25/08/12	Aston Villa	Everton	1	3	A	3.25	3.4	2.6	L	W
25/08/12	Chelsea	Newcastle	2	0	H	1.53	4.55	7	W	L
25/08/12	Man United	Fulham	3	2	H	1.33	5.52	12	W	L
25/08/12	Norwich	QPR	1	1	D	2.38	3.52	3.25	D	D
25/08/12	Southampton	Wigan	0	2	A	2.2	3.53	3.65	L	W
25/08/12	Swansea	West Ham	3	0	H	2.15	3.47	3.85	W	L
25/08/12	Tottenham	West Brom	1	1	D	1.54	4.53	7.5	D	D
26/08/12	Liverpool	Man City	2	2	D	3.15	3.45	2.48	D	D

<tbody>
</tbody>

Data extends up to 3000 rows for some leagues.

Somehow, I would like to do two similar things.

The first is to identify only those games where the home team has WON 4 out of their 5 PREVIOUS home games, AND where the away team has LOST 4 out of PREVIOUS last 5 away games.

Specifically any game where the home teams previous form is WWWWD, WWWDW, WWDWW, WDWWW, DWWWW. The away team form combination is abit more complex with 112 possibilities but basically no more than 1 win in their last 5 games

While the second is to identify only those games where there have been 2 or more goals in 4 of the home teams last 5 games, AND 2 or more goals in 4 of the away teams last 5 away games.

Is this something that is even possible in Excel?

If it is, I'd really appreciate some general advice on how you would go about approaching the task?

 

[jmacleary]

Hi there. I haven't provided a full solution, as you only wanted some guidance, but I have cobbled together a couple of columns that can go towards what you want. I assume your data starts in A1 - for the formulas to work there needs to be an extra blank row at the top, so I have shown data starting in row3. The formula in L3, copied down, give the last 5 results (in reverse order) for the home team - the formula in M3 copied down gives the number of wins in that set. I notice you didn't include examples such as"WWWWL" - if you only want to cater for win/draw scenraios then you will need to modify that formula. A modification of those formulas in N and O gives the away team form, and column P gives a match if both criteria are satisfied. It would be possible to condense the formulae into a single cell, but it would be very complex, so I thought it better to set it out in columns.

Excel 2007 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	Date	HomeTeam	AwayTeam	Home Goals	Away Goals	Result	Home Odds	Draw Odds	Away Odds	H	A	Home pattern		Away Pattern
3	18/08/2012	Arsenal	Sunderland	0	0	D	1.44	4.89	9.5	D	D	D	0	D	0
4	18/08/2012	Chelsea	Swansea	5	0	H	1.85	3.82	4.8	W	L	W	1	L	1
5	18/08/2012	Chelsea	Swansea	2	1	H	2.7	3.47	2.85	W	L	WW	2	LL	2
6	18/08/2012	QPR	Swansea	0	5	A	2.1	3.55	4.2	L	W	L	0	WLL	2
7	18/08/2012	Chelsea	Stoke	1	1	D	2.45	3.4	3.26	D	D	DWW	2	D	0
8	18/08/2012	West Brom	Swansea	3	0	H	4.38	3.61	2	W	L	W	1	LWLL	3
9	18/08/2012	Chelsea	Aston Villa	1	0	H	2.3	3.46	3.51	W	L	WDWW	3	L	1
10	19/08/2012	Man City	Southampton	3	2	H	1.21	7.55	19.38	W	L	W	1	L	1
11	19/08/2012	Wigan	Chelsea	0	2	A	6.75	4	1.67	L	W	L	0	W	0
12	20/08/2012	Everton	Man United	1	0	H	4.45	3.71	1.91	W	L	W	1	L	1
13	22/08/2012	Chelsea	Swansea	4	2	H	1.29	6.2	14	W	L	WWDWW	4	LLWLL	4	Match
14	25/08/2012	Chelsea	Liverpool	1	3	A	3.25	3.4	2.6	L	W	LWWDW	3	W	0

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula L3 =IF([COLOR=rgb(255]ISNA(LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]B$1:B2=B3[/COLOR]),L$1:L2[/COLOR])),J3,MID(J3&LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]B$1:B2=B3[/COLOR]),L$1:L2[/COLOR]),1,5)[/COLOR]) M3 =LEN([COLOR=rgb(255]L3[/COLOR])-LEN([COLOR=rgb(255]SUBSTITUTE(L3,"W","")[/COLOR]) N3 =IF([COLOR=rgb(255]ISNA(LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]C$1:C2=C3[/COLOR]),N$1:N2[/COLOR])),K3,MID(K3&LOOKUP([COLOR=0)]2,1/([COLOR=rgb(0]C$1:C2=C3[/COLOR]),N$1:N2[/COLOR]),1,5)[/COLOR]) O3 =LEN([COLOR=rgb(255]N3[/COLOR])-LEN([COLOR=rgb(255]SUBSTITUTE(N3,"L","")[/COLOR]) P3 =IF([COLOR=rgb(255]AND(M3=4,O3=4),"Match",""[/COLOR]) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jmacleary]

It looks like those formulas adidntcome out right so here they are:
L3 =IF(ISNA(LOOKUP(2,1/(B$1:B2=B3),L$1:L2)),J3,MID(J3&LOOKUP(2,1/(B$1:B2=B3),L$1:L2),1,5))
M3 =LEN(L3)-LEN(SUBSTITUTE(L3,"W",""))
N3 =IF(ISNA(LOOKUP(2,1/(C$1:C2=C3),N$1:N2)),K3,MID(K3&LOOKUP(2,1/(C$1:C2=C3),N$1:N2),1,5))
O3 =LEN(N3)-LEN(SUBSTITUTE(N3,"L",""))
P3 =IF(AND(M3=4,O3=4),"Match","")