How to show last 5 games team's form

[VKiprijan]

Hi Everyone,

I have a soccer league table with rankings in which I would like to show each team's form in the last 5 games played (Wins, Draw, Loss). What formula do I need to use in the columns CT thru CX in order to show this kind of statistics for each team?

Thank you all in advance.

Soccer League.xlsx
	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX
3	German Bundes League
4
5			Ranking System				Total								Home								Away								Last 5 games
6	Ranking	Team	POINTS	GD	GS	GA	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P	GP	W	D	L	GS	GR	GD	P
7	9	Eintracht Frankfurt	9	0	0	0	34	11	10	10	51	49	2	43	17	7	5	3	30	17	13	26	17	4	5	7	21	32	-11	17
8	1	Bayern Munich	1	0	0	0	34	19	8	4	83	34	49	65	17	10	5	0	46	14	32	35	17	9	3	4	37	20	17	30
9	7	Wolfsburg	7	0	0	0	34	12	10	9	54	43	11	46	17	5	7	3	31	22	9	22	17	7	3	6	23	21	2	24
10	12	Werder Bremen	12	0	0	0	34	10	5	16	49	60	-11	35	17	5	1	10	25	27	-2	16	17	5	4	6	24	33	-9	19
11	4	Union Berlin	4	0	0	0	34	16	8	7	44	32	12	56	17	9	6	0	25	9	16	33	17	7	2	7	19	23	-4	23
12	18	Hertha Berlin	18	0	0	0	34	6	7	18	37	62	-25	25	17	5	5	6	23	26	-3	20	17	1	2	12	14	36	-22	5
13	17	Bochum	16	1	0	0	34	8	4	19	33	69	-36	28	17	6	2	7	22	30	-8	20	17	2	2	12	11	39	-28	8
14	8	Mainz	8	0	0	0	34	12	9	10	51	46	5	45	17	6	6	4	30	21	9	24	17	6	3	6	21	25	-4	21
15	10	B. Monchengladbach	10	0	0	0	34	10	9	12	46	48	-2	39	17	9	3	4	31	18	13	30	17	1	6	8	15	30	-15	9
16	14	Hoffenheim	14	0	0	0	34	9	5	17	42	52	-10	32	17	6	2	8	24	27	-3	20	17	3	3	9	18	25	-7	12
17	13	Augsburg	13	0	0	0	34	9	7	15	40	55	-15	34	17	6	4	6	16	21	-5	22	17	3	3	9	24	34	-10	12
18	5	Freiburg	4	1	0	0	34	16	8	7	46	38	8	56	17	9	4	3	26	13	13	31	17	7	4	4	20	25	-5	25
19	2	Dortmund	2	0	0	0	34	20	4	7	73	40	33	64	17	13	1	1	48	13	35	40	17	7	3	6	25	27	-2	24
20	6	Bayer Leverkusen	6	0	0	0	34	14	6	11	54	43	11	48	17	8	2	6	33	21	12	26	17	6	4	5	21	22	-1	22
21	16	Stuttgart	16	0	0	0	34	6	10	15	39	54	-15	28	17	5	4	6	21	20	1	19	17	1	6	9	18	34	-16	9
22	3	RB Leipzig	3	0	0	0	34	17	6	8	55	37	18	57	17	11	2	2	32	15	17	35	17	6	4	6	23	22	1	22
23	11	FC Koln	11	0	0	0	34	9	11	11	42	49	-7	38	17	5	5	5	22	16	6	20	17	4	6	6	20	33	-13	18
24	15	Schalke	15	0	0	0	34	7	9	15	31	59	-28	30	17	5	4	7	21	34	-13	19	17	2	5	8	10	25	-15	11
Bunde League

Cell Formulas
Range	Formula
BR7:BR24	BR7	=RANK(CC7,$CC$7:$CC$24)
BS7:BS24	BS7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($CB$7:$CB$24>CB7))
BT7:BT24	BT7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BZ$7:$BZ$24>BZ7))
BU7:BU24	BU7	=SUMPRODUCT(($BR$7:$BR$24=$BR7)*($BS$7:$BS$24=$BS7)*($BT$7:$BT$24=$BT7)*($CP$7:$CP$24>CP7))
BV7:CC24	BV7	=CD7+CL7
CD7:CD24	CD7	=COUNTIF($C$5:$C$346,BQ7)
CE7:CE24	CE7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CF7:CF24	CF7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($F$5:$F$346<>""))
CG7:CG24	CG7	=SUMPRODUCT(($C$5:$C$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CH7:CH24	CH7	=SUMIF($C$5:$C$346,BQ7,$F$5:$F$346)
CI7:CI24	CI7	=SUMIF($C$5:$C$346,BQ7,$G$5:$G$346)
CJ7:CJ24,CR7:CR24	CJ7	=CH7-CI7
CK7:CK24,CS7:CS24	CK7	=CE7*3+CF7*1
CL7:CL24	CL7	=COUNTIF($H$5:$H$346,BQ7)
CM7:CM24	CM7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346<$G$5:$G$346))
CN7:CN24	CN7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346=$G$5:$G$346)*($G$5:$G$346<>""))
CO7:CO24	CO7	=SUMPRODUCT(($H$5:$H$346=BQ7)*($F$5:$F$346>$G$5:$G$346))
CP7:CP24	CP7	=SUMIF($H$5:$H$346,BQ7,$G$5:$G$346)
CQ7:CQ24	CQ7	=SUMIF($H$5:$H$346,BQ7,$F$5:$F$346)
BP7:BP24	BP7	=SUM(BR7:BU7)

 

[VKiprijan]

See post#28, I already realised the error.

Yes, now the formula for the away games works properly.

Is there a way with a formula or with a conditional formatting to highlight the Wins in green, the Losses in red and the Draws in yellow these last 5 games?

Thanks and apreciate it.

 

[Peter_SSs]

I believe that there was some problem with the formula in my Excel 2016 environment thus not working properly as Peter_SS pointed out.

Did you try the suggestion that I made in post #16 which does use functions available in Excel 2016?

In relation to the Home/Away last 5, still trying to keep the formulas simpler by using a helper column, and including the green/red/yellow for W/L/D you could try this.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	LW																																																																																									Home					Away
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	DD																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	WL																																																											9	Eintracht Frankfurt																													D	D	D	D	W	L	L	L	L	D
8	06.08.2022	Bochum	1	1	1	2	Mainz	LW																																																											1	Bayern Munich																													W	D	W	W	W	W	L	W	L	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	WL																																																											7	Wolfsburg																													W	D	D	D	D	L	W	L	W	W
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	LW																																																											12	Werder Bremen																													L	L	L	L	W	L	W	D	D	L
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	WL																																																											4	Union Berlin																													D	D	W	W	D	L	W	L	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	DD																																																											18	Hertha Berlin																													W	L	L	D	W	L	L	D	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	WL																																																											17	Bochum																													D	L	L	W	L	L	D	D	W	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	LW																																																											8	Mainz																													L	W	D	D	W	L	D	W	D	W
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	DD																																																											10	B. Monchengladbach																													W	L	W	D	D	L	D	D	L	L
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	DD																																																											14	Hoffenheim																													W	L	W	W	L	L	D	W	L	L
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	WL																																																											13	Augsburg																													W	D	L	D	W	D	L	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	DD																																																											5	Freiburg																													L	W	L	D	W	W	W	D	D	W
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	LW																																																											2	Dortmund																													W	W	W	W	W	D	D	L	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	DD																																																											6	Bayer Leverkusen																													L	W	W	W	W	D	D	W	W	D
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	DD																																																											16	Stuttgart																													W	D	L	L	W	L	D	W	L	D
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	WL																																																											3	RB Leipzig																													W	W	L	W	W	W	L	W	L	L
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	WL																																																											11	FC Koln																													L	D	D	L	L	W	W	W	L	D
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	DD																																																											15	Schalke																													W	W	L	D	W	W	L	L	D	W
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	LW
Home Away

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LEFT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($C$5:$C$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
CY7:DC24	CY7	=RIGHT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($H$5:$H$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
I5:I25	I5	=MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
CT7:DC24	Expression	=CT7="D"	text	NO
CT7:DC24	Expression	=CT7="L"	text	NO
CT7:DC24	Expression	=CT7="W"	text	NO

 

[VKiprijan]

Did you try the suggestion that I made in post #16 which does use functions available in Excel 2016?

In relation to the Home/Away last 5, still trying to keep the formulas simpler by using a helper column, and including the green/red/yellow for W/L/D you could try this.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	LW																																																																																									Home					Away
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	DD																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	WL																																																											9	Eintracht Frankfurt																													D	D	D	D	W	L	L	L	L	D
8	06.08.2022	Bochum	1	1	1	2	Mainz	LW																																																											1	Bayern Munich																													W	D	W	W	W	W	L	W	L	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	WL																																																											7	Wolfsburg																													W	D	D	D	D	L	W	L	W	W
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	LW																																																											12	Werder Bremen																													L	L	L	L	W	L	W	D	D	L
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	WL																																																											4	Union Berlin																													D	D	W	W	D	L	W	L	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	DD																																																											18	Hertha Berlin																													W	L	L	D	W	L	L	D	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	WL																																																											17	Bochum																													D	L	L	W	L	L	D	D	W	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	LW																																																											8	Mainz																													L	W	D	D	W	L	D	W	D	W
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	DD																																																											10	B. Monchengladbach																													W	L	W	D	D	L	D	D	L	L
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	DD																																																											14	Hoffenheim																													W	L	W	W	L	L	D	W	L	L
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	WL																																																											13	Augsburg																													W	D	L	D	W	D	L	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	DD																																																											5	Freiburg																													L	W	L	D	W	W	W	D	D	W
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	LW																																																											2	Dortmund																													W	W	W	W	W	D	D	L	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	DD																																																											6	Bayer Leverkusen																													L	W	W	W	W	D	D	W	W	D
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	DD																																																											16	Stuttgart																													W	D	L	L	W	L	D	W	L	D
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	WL																																																											3	RB Leipzig																													W	W	L	W	W	W	L	W	L	L
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	WL																																																											11	FC Koln																													L	D	D	L	L	W	W	W	L	D
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	DD																																																											15	Schalke																													W	W	L	D	W	W	L	L	D	W
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	LW
Home Away

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LEFT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($C$5:$C$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
CY7:DC24	CY7	=RIGHT(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/(($H$5:$H$500=$BQ7)*($B$5:$B$500<>"")),COLUMNS($CT:CT))),1)
I5:I25	I5	=MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
CT7:DC24	Expression	=CT7="D"	text	NO
CT7:DC24	Expression	=CT7="L"	text	NO
CT7:DC24	Expression	=CT7="W"	text	NO

Yes, I tried the solution posted in the Post #16 and it worked.

The formula in column "I" also worked but I have difficulties how to highlight these result letters with the conditional formatting. When I go to Conditional formatting>HIghlight Cells Rules>Text that Contains here I cannot put both the ranges CT7:DC24 and the condition =CT7="D" since I only have one line "Format cells that contain the text". Where do I need to put these parameters: =CT7="D","L" and "W"?

Thanks.

 

[Peter_SSs]

Yes, I tried the solution posted in the Post #16 and it worked.

The formula in column "I" also worked

OK. Thanks for the confirmation.

.. but I have difficulties how to highlight these result letters

Try it this way. Remove any existing conditional formatting from CT7:DC24 and with that range selected try this "Use a formula .. " method instead.
You need a separate rule for each letter.

 

[VKiprijan]

OK. Thanks for the confirmation.


Try it this way. Remove any existing conditional formatting from CT7:DC24 and with that range selected try this "Use a formula .. " method instead.
You need a separate rule for each letter.

View attachment 91855

Yes, I've put all three conditions separately and it finally works perfect.

Thank you so much.

 

[VKiprijan]

Do you mean in my results? You listed those blue results as the correct result for Bayern Munich and that is exactly what my results showed:

View attachment 91681

Having said that, I have realised that I used functions in my formulas that you will not have in your 2016 version.

Here is an alternative, still using a helper column (which you can hide) to keep the formulas a bit shorter, that I think should work in your version.

VKiprijan.xlsm
	B	C	D	E	F	G	H	I																																																											BP	BQ																													CT	CU	CV	CW	CX
3	Round																																																																		German Bundes League
4	Date	Home	1st Half		2nd Half		Away
5	05.08.2022	Eintracht Frankfurt	0	5	1	6	Bayern Munich	|Eintracht Frankfurt|L|Bayern Munich|W																																																																																									Last 5 games
6	06.08.2022	Wolfsburg	1	2	2	2	Werder Bremen	|Wolfsburg|D|Werder Bremen|D																																																											Ranking	Team
7	06.08.2022	Union Berlin	1	0	3	1	Hertha Berlin	|Union Berlin|W|Hertha Berlin|L																																																											9	Eintracht Frankfurt																													L	D	L	D	L
8	06.08.2022	Bochum	1	1	1	2	Mainz	|Bochum|L|Mainz|W																																																											1	Bayern Munich																													W	W	L	D	W
9	06.08.2022	B. Monchengladbach	1	1	3	1	Hoffenheim	|B. Monchengladbach|W|Hoffenheim|L																																																											7	Wolfsburg																													L	W	W	D	L
10	06.08.2022	Augsburg	0	0	0	4	Freiburg	|Augsburg|L|Freiburg|W																																																											12	Werder Bremen																													L	L	W	L	D
11	06.08.2022	Dortmund	1	0	1	0	Bayer Leverkusen	|Dortmund|W|Bayer Leverkusen|L																																																											4	Union Berlin																													L	D	W	D	L
12	07.08.2022	Stuttgart	1	1	1	1	RB Leipzig	|Stuttgart|D|RB Leipzig|D																																																											18	Hertha Berlin																													W	L	L	L	L
13	07.08.2022	FC Koln	0	0	3	1	Schalke	|FC Koln|W|Schalke|L																																																											17	Bochum																													L	D	L	D	L
14	12.08.2022	Freiburg	1	0	1	3	Dortmund	|Freiburg|L|Dortmund|W																																																											8	Mainz																													L	L	W	D	D
15	13.08.2022	Werder Bremen	1	1	2	2	Stuttgart	|Werder Bremen|D|Stuttgart|D																																																											10	B. Monchengladbach																													W	L	L	D	W
16	13.08.2022	RB Leipzig	1	1	2	2	FC Koln	|RB Leipzig|D|FC Koln|D																																																											14	Hoffenheim																													W	L	L	D	W
17	13.08.2022	Hoffenheim	2	2	3	2	Bochum	|Hoffenheim|W|Bochum|L																																																											13	Augsburg																													W	D	D	L	L
18	13.08.2022	Hertha Berlin	1	0	1	1	Eintracht Frankfurt	|Hertha Berlin|D|Eintracht Frankfurt|D																																																											5	Freiburg																													L	W	W	W	L
19	13.08.2022	Bayer Leverkusen	1	1	1	2	Augsburg	|Bayer Leverkusen|L|Augsburg|W																																																											2	Dortmund																													W	D	W	D	W
20	13.08.2022	Schalke	1	0	2	2	B. Monchengladbach	|Schalke|D|B. Monchengladbach|D																																																											6	Bayer Leverkusen																													L	D	W	D	W
21	14.08.2022	Mainz	0	0	0	0	Union Berlin	|Mainz|D|Union Berlin|D																																																											16	Stuttgart																													L	W	D	D	W
22	14.08.2022	Bayern Munich	2	0	2	0	Wolfsburg	|Bayern Munich|W|Wolfsburg|L																																																											3	RB Leipzig																													W	W	L	W	W
23	19.08.2022	B. Monchengladbach	1	0	1	0	Hertha Berlin	|B. Monchengladbach|W|Hertha Berlin|L																																																											11	FC Koln																													W	L	W	D	W
24	20.08.2022	Wolfsburg	0	0	0	0	Schalke	|Wolfsburg|D|Schalke|D																																																											15	Schalke																													W	W	L	W	L
25	20.08.2022	Stuttgart	0	1	0	1	Freiburg	|Stuttgart|L|Freiburg|W
26	20.08.2022	Dortmund	1	0	2	3	Werder Bremen	|Dortmund|L|Werder Bremen|W
Sheet3

Cell Formulas
Range	Formula
CT7:CX24	CT7	=LEFT(TRIM(RIGHT(SUBSTITUTE(INDEX($I:$I,AGGREGATE(14,6,ROW($I$5:$I$500)/ISNUMBER(SEARCH("|"&$BQ7&"|",$I$5:$I$500)),COLUMNS($CT:CT))),"|"&$BQ7&"|",REPT(" ",100)),100)),1)
I5:I26	I5	=IF(B5="","","|"&C5&"|"&MID("LDW",SIGN(F5-G5)+2,1)&"|"&H5&"|"&MID("WDL",SIGN(F5-G5)+2,1))

What formula in the helper column "I" should I put to show me only the results for the games that have been played not for all the games in the fixtures? Otherwise for the games that were not played yet it shows me "DD".

Thanks in advance.

 

[Peter_SSs]

What formula in the helper column "I" should I put to show me only the results for the games that have been played

Well, you could use ..

=IF(B5="","",MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1))

But you could also keep the existing formula and simply hide column I since it is just a helper column for the other formulas.

 

[VKiprijan]

Well, you could use ..

=IF(B5="","",MID("LDW",SIGN(F5-G5)+2,1)&MID("WDL",SIGN(F5-G5)+2,1))

But you could also keep the existing formula and simply hide column I since it is just a helper column for the other formulas.

But, I don't want to use the B column which is used for dates of the games, I would rather want to use columns F and G (final result) as a main indicator that a game was played. How do I use these two columns in the formula, before the MID i tried to insert =IF(F<>"",G<>"" but it didn't worked.

Thanks.

 

[Peter_SSs]

i tried to insert =IF(F<>"",G<>""

Why would you need to check both columns? Surely if a game has not been played, neither column would have a number in so just checking one column for blank should be sufficient?

 

[VKiprijan]

Why would you need to check both columns? Surely if a game has not been played, neither column would have a number in so just checking one column for blank should be sufficient?

OK, you're right, I could use just the column B as a main indicator if the game was played, otherwise it will be just overcomplicating things.

Thanks a lot.