football Recent Form

[bonezvillefc]

My excel workbook scrape results from Betstudy.com (VBA). I was able to create league table from the results for all the games played this season. I am looking for a formula to generate the last 5 and 10 games played by each team both at home and away. Also my results sheet scrape postponed games (appearing as Pstp in a column) and my league table recognize these postponed games as played games. I would like another formula to ignore these games. Help for this will be highly appreciated

Betstudy Form Guide v1.6 - Copy - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Date	Home Team	Away Team	Home Goals	Away Goals		Teams	Home Goals	Column2	Column3	Column4	URL:	https://www.betstudy.com/soccer-stats/c/spain/copa-del-rey/d/results/2021-2022/
2	27.02.2022	West Ham United	Wolverhampton Wanderers	1	0		West Ham United	25	21	46	Under 2.5					URL List
3	27.02.2022	Chelsea	Leicester City	Pstp			Wolverhampton Wanderers	10	14	24	#VALUE!					England - 2021/2022 Championship results
4	26.02.2022	Everton	Manchester City	0	1		Chelsea	27	22	49	Under 2.5					South Africa - 2021/2022 PSL results
5	26.02.2022	Brighton & Hove Albion	Aston Villa	0	2		Leicester City	21	16	37	Under 2.5					England - 2021/2022 Premier League results
6	26.02.2022	Manchester United	Watford	0	0		Everton	18	10	28	Under 2.5	League	Eng PL			Spain - 2021/2022 Primera División results
7	26.02.2022	Crystal Palace	Burnley	1	1		Manchester City	36	28	64	Under 2.5					Germany - 2021/2022 Bundesliga results
8	26.02.2022	Brentford	Newcastle United	0	2		Brighton & Hove Albion	10	15	25	Under 2.5					Italy - 2021/2022 Serie A results
9	26.02.2022	Leeds United	Tottenham Hotspur	0	4		Aston Villa	20	13	33	over2.5					https://www.betstudy.com/soccer-stats/c/france/ligue-1/results/2021-2022/
10	25.02.2022	Southampton	Norwich City	2	0		Manchester United	21	23	44	Under 2.5					Portugal - 2021/2022 Primeira Liga results
11	24.02.2022	Arsenal	Wolverhampton Wanderers	2	1		Watford	12	13	25	over2.5					USA - 2022 MLS results
12	23.02.2022	Liverpool	Leeds United	6	0		Crystal Palace	22	15	37	over2.5					Sweden - 2022 Allsvenskan results
13	23.02.2022	Watford	Crystal Palace	1	4	-3	Burnley	10	12	22	over2.5					Brazil - 2022 Serie A results
14	23.02.2022	Burnley	Tottenham Hotspur	1	0	1	Brentford	14	13	27	Under 2.5					https://www.betstudy.com/soccer-stats/c/denmark/superliga/d/results/2021-2022/
15	20.02.2022	Wolverhampton Wanderers	Leicester City	2	1	1	Newcastle United	18	10	28	over2.5					France - 2021/2022 Ligue 2 results
16	20.02.2022	Leeds United	Manchester United	2	4	-2	Leeds United	15	14	29	over2.5					Netherlands - 2021/2022 Eredivisie results
17	19.02.2022	Manchester City	Tottenham Hotspur	2	3	-1	Tottenham Hotspur	18	17	35	over2.5					Netherlands - 2021/2022 Eerste Divisie results
18	19.02.2022	Southampton	Everton	2	0	2	Southampton	18	16	34	Under 2.5					Italy - 2021/2022 Serie B results
19	19.02.2022	Liverpool	Norwich City	3	1	2	Norwich City	8	7	15	over2.5					Spain - 2021/2022 Primera División results
20	19.02.2022	Crystal Palace	Chelsea	0	1	-1	Arsenal	22	16	38	Under 2.5					Israel - 2021/2022 Ligat ha'Al results
21	19.02.2022	Brighton & Hove Albion	Burnley	0	3	-3	Liverpool	36	34	70	over2.5					Turkey - 2021/2022 Süper Lig results
22	19.02.2022	Aston Villa	Watford	0	1	-1		0	0	0	Under 2.5
23	19.02.2022	Arsenal	Brentford	2	1	1		0	0	0	over2.5					League List
24	19.02.2022	West Ham United	Newcastle United	1	1	0		0	0	0	Under 2.5					Eng Championship
25	15.02.2022	Manchester United	Brighton & Hove Albion	2	0	2		0	0	0	Under 2.5					Eng PL
26	13.02.2022	Leicester City	West Ham United	2	2	0		381	329	710	over2.5					SA PSL
27	13.02.2022	Tottenham Hotspur	Wolverhampton Wanderers	0	2	-2					Under 2.5					Spain Primera
28	13.02.2022	Newcastle United	Aston Villa	1	0	1					Under 2.5					Germany Bundesliga
29	13.02.2022	Burnley	Liverpool	0	1	-1					Under 2.5					Italy Serie A
30	12.02.2022	Norwich City	Manchester City	0	4	-4					over2.5					Fra Ligue
31	12.02.2022	Watford	Brighton & Hove Albion	0	2	-2					Under 2.5					Portugal Primeira
32	12.02.2022	Everton	Leeds United	3	0	3					over2.5					USA MLS
33	12.02.2022	Chelsea	Arsenal	Pstp		#VALUE!					#VALUE!					Sweden
34	12.02.2022	Brentford	Crystal Palace	0	0	0					Under 2.5					Brazil Serie
35	12.02.2022	Manchester United	Southampton	1	1	0					Under 2.5					Spain Laliga
36	10.02.2022	Liverpool	Leicester City	2	0	2					Under 2.5					Denmark Superliga
37	10.02.2022	Wolverhampton Wanderers	Arsenal	0	1	-1					Under 2.5					Fra Ligue2
38	09.02.2022	Aston Villa	Leeds United	3	3	0					over2.5					Ned Eredivisie
39	09.02.2022	Manchester City	Brentford	2	0	2					Under 2.5					Ned Eerste
40	09.02.2022	Tottenham Hotspur	Southampton	2	3	-1					over2.5					Italy Serie B
41	09.02.2022	Norwich City	Crystal Palace	1	1	0					Under 2.5					Spain Liga2
42	08.02.2022	Burnley	Manchester United	1	1	0					Under 2.5					Israel Ligat
43	08.02.2022	Newcastle United	Everton	3	1	2					over2.5					Turkey Super
44	08.02.2022	West Ham United	Watford	1	0	1					Under 2.5
45	05.02.2022	Burnley	Watford	0	0	0					Under 2.5
46	23.01.2022	Chelsea	Tottenham Hotspur	2	0	2					Under 2.5
47	23.01.2022	Leicester City	Brighton & Hove Albion	1	1	0					Under 2.5
48	23.01.2022	Crystal Palace	Liverpool	1	3	-2					over2.5
49	23.01.2022	Arsenal	Burnley	0	0	0					Under 2.5
50	22.01.2022	Southampton	Manchester City	1	1	0					Under 2.5
51	22.01.2022	Manchester United	West Ham United	1	0	1					Under 2.5
52	22.01.2022	Leeds United	Newcastle United	0	1	-1					Under 2.5
53	22.01.2022	Brentford	Wolverhampton Wanderers	1	2	-1					over2.5
54	22.01.2022	Everton	Aston Villa	0	1	-1					Under 2.5
55	21.01.2022	Watford	Norwich City	0	3	-3					over2.5
56	19.01.2022	Brentford	Manchester United	1	3	-2					over2.5
57	19.01.2022	Leicester City	Tottenham Hotspur	2	3	-1					over2.5
58	18.01.2022	Brighton & Hove Albion	Chelsea	1	1	0					Under 2.5
59	16.01.2022	Tottenham Hotspur	Arsenal	Pstp		#VALUE!					#VALUE!
60	16.01.2022	West Ham United	Leeds United	2	3	-1					over2.5
61	16.01.2022	Liverpool	Brentford	3	0	3					over2.5
62	15.01.2022	Aston Villa	Manchester United	2	2	0					over2.5
63	15.01.2022	Wolverhampton Wanderers	Southampton	3	1	2					over2.5
64	15.01.2022	Norwich City	Everton	2	1	1					over2.5
65	15.01.2022	Newcastle United	Watford	1	1	0					Under 2.5
66	15.01.2022	Manchester City	Chelsea	1	0	1					Under 2.5
67	14.01.2022	Brighton & Hove Albion	Crystal Palace	1	1	0					Under 2.5
68	12.01.2022	West Ham United	Norwich City	2	0	2					Under 2.5
69	11.01.2022	Everton	Leicester City	Pstp		#VALUE!					#VALUE!
70	11.01.2022	Southampton	Brentford	4	1	3					over2.5
71	03.01.2022	Manchester United	Wolverhampton Wanderers	0	1	-1					Under 2.5
72	02.01.2022	Chelsea	Liverpool	2	2	0					over2.5
73	02.01.2022	Leeds United	Burnley	3	1	2					over2.5
74	02.01.2022	Everton	Brighton & Hove Albion	2	3	-1					over2.5
75	02.01.2022	Brentford	Aston Villa	2	1	1					over2.5
76	01.01.2022	Crystal Palace	West Ham United	2	3	-1					over2.5
77	01.01.2022	Watford	Tottenham Hotspur	0	1	-1					Under 2.5
78	01.01.2022	Leicester City	Norwich City	Pstp		#VALUE!					#VALUE!
79	01.01.2022	Arsenal	Manchester City	1	2	-1					over2.5
80	30.12.2021	Manchester United	Burnley	3	1	2					over2.5
81	29.12.2021	Brentford	Manchester City	0	1	-1					Under 2.5
82	29.12.2021	Chelsea	Brighton & Hove Albion	1	1	0					Under 2.5
83	28.12.2021	Leicester City	Liverpool	1	0	1					Under 2.5
84	28.12.2021	Watford	West Ham United	1	4	-3					over2.5
85	28.12.2021	Southampton	Tottenham Hotspur	1	1	0					Under 2.5
86	28.12.2021	Crystal Palace	Norwich City	3	0	3					over2.5
87	27.12.2021	Newcastle United	Manchester United	1	1	0					Under 2.5
88	26.12.2021	Brighton & Hove Albion	Brentford	2	0	2					Under 2.5
89	26.12.2021	Aston Villa	Chelsea	1	3	-2					over2.5
90	26.12.2021	West Ham United	Southampton	2	3	-1					over2.5
91	26.12.2021	Tottenham Hotspur	Crystal Palace	3	0	3					over2.5
92	26.12.2021	Norwich City	Arsenal	0	5	-5					over2.5
93	26.12.2021	Manchester City	Leicester City	6	3	3					over2.5
94	26.12.2021	Burnley	Everton	Pstp		#VALUE!					#VALUE!
95	19.12.2021	Tottenham Hotspur	Liverpool	2	2	0					over2.5
96	19.12.2021	Wolverhampton Wanderers	Chelsea	0	0	0					Under 2.5
97	19.12.2021	Newcastle United	Manchester City	0	4	-4					over2.5
98	18.12.2021	Leeds United	Arsenal	1	4	-3					over2.5
99	18.12.2021	Aston Villa	Burnley	Pstp		#VALUE!					#VALUE!
100	16.12.2021	Liverpool	Newcastle United	3	1	2					over2.5
RESULTS

Cell Formulas
Range	Formula
H2:H25	H2	=SUMIF($B$2:$B$600,$G2,$D$2:$D$600)
I2:I25	I2	=SUMIF($C$2:$C$600,$G2,$E$2:$E$600)
J2:J25	J2	=SUM(RESULTS!$H2:$I2)
H26	H26	=SUBTOTAL(109,RESULTS!$H$2:$H$25)
I26	I26	=SUBTOTAL(109,RESULTS!$I$2:$I$25)
J26	J26	=SUBTOTAL(109,RESULTS!$J$2:$J$25)
F13:F100	F13	=D13-E13
K2:K100	K2	=IF(D2+E2>=3,"over2.5","Under 2.5")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	contains "metz"	text	NO
B:B	Cell Value	contains "udinese"	text	NO
C:C	Cell Value	contains "sALERNITANA"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
M6	List	=$P$24:$P$43

 

[mackc557]

Column F: IF(AND(D2<>"Pstp",E2<>"Pstp"),D2-E2,"Ptsp")
Column K: IF(AND(D2<>"Pstp",E2<>"Pstp"),IF(D2+E2>=3,"over2.5","Under 2.5"),"Ptsp")

Just adding in a condition for postponement. I used not-postponed because you are more likely to have a game not be postponed, and a good practice is to have your most likely event be the true statement since it's called first (fewer steps in the processing).

 

[bonezvillefc]

Nice one..it worked. Still have to take care of column B C D & E as it is recognized as a game played in my league table
as of now Chelsea played 12 games @ home and this season 2 matches were postponed so my table show that it played 14 games instead of 12
..and because scraping shows Pstp instead of score the table takes it as a loss, so totaling 3 losses . Chelsea only lost 1 game this season at home

yet Thanks for the formulas, they working super fine so far

Hopefully i can work around them to come up with other solutions

 

[mackc557]

Do you have an XL2BB for me of that sheet?
For the "Played" column I would use

COUNTIFS(Results!B2:B100, REFERENCE TO CELL WITH TEAM NAME, Results!D2:D100, "<>Pstp") + COUNTIFS(Results!C2:C100, REFERENCE TO CELL WITH TEAM NAME, Results!D2:D100, "<>Pstp")

If you are getting your losses from "Played" - ("W" + "D") then this should fix both in one. Else please post you XL2BB.

 

[bonezvillefc]

Looks like it could work. fixed chelsea but messed up other teams' games

Betstudy Form Guide v1.6 - Copy - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1			HOME GAMES												AWAY GAMES
2	Teams		Played	W	D	L	F	A	Pts	GD		Played	W	D	L	F	A	Pts	GD
3	West Ham United		14	7	3	4	25	19	24	6		13	6	3	4	21	15	21	6
4	Wolverhampton Wanderers		12	5	2	5	10	10	17	0		14	7	2	5	14	11	23	3
5	Chelsea		12	6	5	1	27	10	23	17		13	8	3	2	22	8	27	14
6	Leicester City		12	5	3	4	21	18	18	3		13	2	3	8	16	25	9	-9
7	Everton		12	5	1	6	18	18	16	0		13	1	3	9	10	23	6	-13
8	Manchester City		13	10	1	2	36	9	31	27		14	11	2	1	28	8	35	20
9	Brighton & Hove Albion		13	3	5	5	10	16	14	-6		13	4	7	2	15	14	19	1
10	Aston Villa		12	4	3	5	20	20	15	0		13	5	0	8	13	17	15	-4
11	Manchester United		14	7	3	4	21	16	24	5		13	6	5	2	23	18	23	5
12	Watford		13	2	1	10	12	31	7	-19		13	3	3	7	13	16	12	-3
13	Crystal Palace		14	4	6	4	22	17	18	5		13	2	6	5	15	21	12	-6
14	Burnley		11	2	6	3	10	10	12	0		14	1	6	7	12	20	9	-8
15	Brentford		14	4	2	8	14	19	14	-5		13	2	4	7	13	25	10	-12
16	Newcastle United		13	3	6	4	18	24	15	-6		12	2	4	6	10	21	10	-11
17	Leeds United		13	3	4	6	15	25	13	-10		13	2	4	7	14	35	10	-21
18	Tottenham Hotspur		12	7	1	4	18	15	22	3		13	6	2	5	17	17	20	0
19	Southampton		13	5	7	1	18	10	22	8		13	3	4	6	16	27	13	-11
20	Norwich City		13	2	3	8	8	25	9	-17		14	2	2	10	7	30	8	-23
21	Arsenal		13	9	2	2	22	10	29	12		13	5	1	7	16	17	16	-1
22	Liverpool		13	10	3	0	36	7	33	29		13	8	3	2	34	13	27	21
23	0		0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0
24	0		0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0
25	0		0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0
26	0		0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0
CALCULATIONS

Cell Formulas
Range	Formula
C3:C26	C3	=COUNTIFS(RESULTS!$B$2:$B$552,CALCULATIONS!A3,RESULTS!$D$2:$D$552,"<>Pstp")
D3:D26	D3	=COUNTIFS(RESULTS!$B$2:$B$552,CALCULATIONS!A3,RESULTS!$F$2:$F$552,">0")
E3:E26	E3	=COUNTIFS(RESULTS!$B$2:$B$552,CALCULATIONS!A3,RESULTS!$F$2:$F$552,0)
O3:O26,F3:F26	F3	=C3-D3-E3
G3:G26	G3	=SUMIFS(RESULTS!$D$2:$D$552,RESULTS!$B$2:$B$552,CALCULATIONS!A3)
H3:H26	H3	=SUMIFS(RESULTS!$E$2:$E$552,RESULTS!$B$2:$B$552,CALCULATIONS!A3)
R3:R26,I3:I26	I3	=D3*3+E3*1
S3:S26,J3:J26	J3	=G3-H3
L3:L26	L3	=COUNTIFS(RESULTS!$C$2:$C$552,A3,RESULTS!$D$2:$D$552,"<>Ptsp")
M3:M26	M3	=COUNTIFS(RESULTS!$C$2:$C$552,CALCULATIONS!A3,RESULTS!$F$2:$F$552,"<0")
N3:N26	N3	=COUNTIFS(RESULTS!$C$2:$C$552,CALCULATIONS!A3,RESULTS!$F$2:$F$552,0)
P3:P26	P3	=SUMIFS(RESULTS!$E$2:$E$552,RESULTS!$C$2:$C$552,CALCULATIONS!A3)
Q3:Q26	Q3	=SUMIFS(RESULTS!$D$2:$D$552,RESULTS!$C$2:$C$552,CALCULATIONS!A3)
A3:A26	A3	=RESULTS!G2

Betstudy Form Guide v1.6 - Copy - Copy.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Home Team	Away Team	Home Goals	Away Goals	GD	Teams	Home Goals	Column2	Column3	Column4	URL:	https://www.betstudy.com/soccer-stats/c/spain/copa-del-rey/d/results/2021-2022/
2	27.02.2022	West Ham United	Wolverhampton Wanderers	1	0	1	West Ham United	25	21	46	Under 2.5
3	27.02.2022	Chelsea	Leicester City	Pstp		Ptsp	Wolverhampton Wanderers	10	14	24	Ptsp
4	26.02.2022	Everton	Manchester City	0	1	-1	Chelsea	27	22	49	Under 2.5
5	26.02.2022	Brighton & Hove Albion	Aston Villa	0	2	-2	Leicester City	21	16	37	Under 2.5
6	26.02.2022	Manchester United	Watford	0	0	0	Everton	18	10	28	Under 2.5	League	Eng PL
7	26.02.2022	Crystal Palace	Burnley	1	1	0	Manchester City	36	28	64	Under 2.5
8	26.02.2022	Brentford	Newcastle United	0	2	-2	Brighton & Hove Albion	10	15	25	Under 2.5
9	26.02.2022	Leeds United	Tottenham Hotspur	0	4	-4	Aston Villa	20	13	33	over2.5
10	25.02.2022	Southampton	Norwich City	2	0	2	Manchester United	21	23	44	Under 2.5
11	24.02.2022	Arsenal	Wolverhampton Wanderers	2	1	1	Watford	12	13	25	over2.5
12	23.02.2022	Liverpool	Leeds United	6	0	6	Crystal Palace	22	15	37	over2.5
13	23.02.2022	Watford	Crystal Palace	1	4	-3	Burnley	10	12	22	over2.5
14	23.02.2022	Burnley	Tottenham Hotspur	1	0	1	Brentford	14	13	27	Under 2.5
15	20.02.2022	Wolverhampton Wanderers	Leicester City	2	1	1	Newcastle United	18	10	28	over2.5
16	20.02.2022	Leeds United	Manchester United	2	4	-2	Leeds United	15	14	29	over2.5
17	19.02.2022	Manchester City	Tottenham Hotspur	2	3	-1	Tottenham Hotspur	18	17	35	over2.5
18	19.02.2022	Southampton	Everton	2	0	2	Southampton	18	16	34	Under 2.5
19	19.02.2022	Liverpool	Norwich City	3	1	2	Norwich City	8	7	15	over2.5
20	19.02.2022	Crystal Palace	Chelsea	0	1	-1	Arsenal	22	16	38	Under 2.5
21	19.02.2022	Brighton & Hove Albion	Burnley	0	3	-3	Liverpool	36	34	70	over2.5
22	19.02.2022	Aston Villa	Watford	0	1	-1		0	0	0	Under 2.5
23	19.02.2022	Arsenal	Brentford	2	1	1		0	0	0	over2.5
24	19.02.2022	West Ham United	Newcastle United	1	1	0		0	0	0	Under 2.5
25	15.02.2022	Manchester United	Brighton & Hove Albion	2	0	2		0	0	0	Under 2.5
26	13.02.2022	Leicester City	West Ham United	2	2	0		381	329	710	over2.5
27	13.02.2022	Tottenham Hotspur	Wolverhampton Wanderers	0	2	-2					Under 2.5
28	13.02.2022	Newcastle United	Aston Villa	1	0	1					Under 2.5
29	13.02.2022	Burnley	Liverpool	0	1	-1					Under 2.5
30	12.02.2022	Norwich City	Manchester City	0	4	-4					over2.5
31	12.02.2022	Watford	Brighton & Hove Albion	0	2	-2					Under 2.5
32	12.02.2022	Everton	Leeds United	3	0	3					over2.5
33	12.02.2022	Chelsea	Arsenal	Pstp		Ptsp					Ptsp
RESULTS

Cell Formulas
Range	Formula
H2:H25	H2	=SUMIF($B$2:$B$600,$G2,$D$2:$D$600)
I2:I25	I2	=SUMIF($C$2:$C$600,$G2,$E$2:$E$600)
J2:J25	J2	=SUM(RESULTS!$H2:$I2)
H26	H26	=SUBTOTAL(109,RESULTS!$H$2:$H$25)
I26	I26	=SUBTOTAL(109,RESULTS!$I$2:$I$25)
J26	J26	=SUBTOTAL(109,RESULTS!$J$2:$J$25)
F2:F33	F2	= IF(AND(D2<>"Pstp",E2<>"Pstp"),D2-E2,"Ptsp")
K2:K33	K2	=IF(AND(D2<>"Pstp",E2<>"Pstp"),IF(D2+E2>=3,"over2.5","Under 2.5"),"Ptsp")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	contains "metz"	text	NO
B:B	Cell Value	contains "udinese"	text	NO
C:C	Cell Value	contains "sALERNITANA"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
M6	List	=$P$24:$P$43

 

[bonezvillefc]

highlighted with green is correct white is incorrect

 

[mackc557]

It all looks to be in working order. Check on RESULTS that all instances of "Watford" and "Burnley" are spelled correctly, have the same capitalization, and do not have extra spaces or characters.

 

[bonezvillefc]

It all looks to be in working order. Check on RESULTS that all instances of "Watford" and "Burnley" are spelled correctly, have the same capitalization, and do not have extra spaces or characters.

I dont think there is a spelling problem cause everything comes from the web. nothing was entered manually

 

[mackc557]

I don't have YOUR data set in YOUR sheets with ALL of YOUR formulas, so I'm not entirely sure where the issue is coming from.
That being said, if it were me, I would set up filters on RESULTS, filter for only one team for Home Team, look to find any data that would cause an issue, move on to the next team until all teams are done, them move on to Away Team doing the same thing. It can be a long process of problem solving, but it might be necessary.

 

[bonezvillefc]

I don't have YOUR data set in YOUR sheets with ALL of YOUR formulas, so I'm not entirely sure where the issue is coming from.
That being said, if it were me, I would set up filters on RESULTS, filter for only one team for Home Team, look to find any data that would cause an issue, move on to the next team until all teams are done, them move on to Away Team doing the same thing. It can be a long process of problem solving, but it might be necessary.

how do i send the data set and all formulars...i am new here