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)

 

[Peter_SSs]

I could use just the column B as a main indicator if the game was played,

That seems a logical way to me ..

otherwise it will be just overcomplicating things.

.. but it would not be any more complicated to use column D (or E or F or G)

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

 

[VKiprijan]

That seems a logical way to me ..


.. but it would not be any more complicated to use column D (or E or F or G)

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

I've changed to D5 as per your suggestion so it does the job. Thanks a lot.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, here is a simpler formula for column I that you could also consider.

VKiprijan.xlsm
	C	D	E	F	G	H	I
5	Eintracht Frankfurt	0	5	1	6	Bayern Munich	LW
6	Wolfsburg	1	2	2	2	Werder Bremen	DD
7	Union Berlin	1	0	3	1	Hertha Berlin	WL
8	Bochum	1	1	1	2	Mainz	LW
9	B. Monchengladbach	1	1	3	1	Hoffenheim	WL
10	Augsburg	0	0	0	4	Freiburg	LW
Home Away

Cell Formulas
Range	Formula
I5:I10	I5	=IF(F5="","",MID("LWDDWL",SIGN(F5-G5)*2+3,2))