Calculations on Last (x) Number of rows

[ormy28]

Hi

I have a spreadsheet that contains football scores contained in a table. Columns A and B contain the home and away teams, and C to F show the number of goals scored in each half by both the home and away teams. A little difficult to explain in writing, but hopefully the below makes it a bit clearer!

	A	B	C	D	E	F	G	H	I	J	K	L
1	Home Team	Away Team	Home Team 1stHalf	Away Team 1stHalf	Home Team 2nd Half	Away Team 2nd Half	Home 2nd Half Y/N	Away 2ndHalf Y/N		Team	Last 5 No of Games (2ndH)	Last 5 No of Goals (2ndH)
2	Bolton	Leicester	1	0	2	1	Y	Y		Bolton	3	7
3	Carlisle	Bolton	1	1	0	1	N	Y
4	Bolton	Huddersfield	2	1	1	1	Y	Y
5	Newcastle	Bolton	0	1	0	0	N	N
6	Torquay	Bolton	1	1	0	2	N	Y
7	Bolton	Grimsby	1	0	0	0	N	N
8	Bolton	Exeter	1	2	3	1	Y	Y
9	QPR	Bolton	1	1	1	2	Y	Y
Sheet2

Cell Formulas
Range	Formula
G2:H9	G2	=IF(E2>0,"Y","N")

The figures in columns K and L show:
K: The number of times the team in J2 have scored in the 2nd half, over the last 5 matches.
L: The number of goals the team in J2 have scored in the 2nd half, over the last 5 matches.

I have calculated the figures in columns K and L manually, but would like to know if there is a formula that would calculate these automatically. I assume an array formula is required. I have dug out an old thread from a few years back where I asked something similar, and have adapted that solution to calculate the figure for column L. Although this appears to calculate where both teams have scored in the 2nd half, rather than just the team in J2, I believe it might be a starting point:

{=SUM(((((Table4[[#Data],[Home Team]]=J2)+(Table4[[#Data],[Away Team]]=J2))*ROW(Table4[[#Data],[Home Team]]))>LARGE(((Table4[[#Data],[Home Team]]=J2)+(Table4[[#Data],[Away Team]]=J2))*ROW(Table4[[#Data],[Home Team]]),6))*((Table4[[#Data],[Home Team 2nd Half]]>0)*(Table4[[#Data],[Away Team 2nd Half]]>0)))}

I believe the first part in the non-bold text might be correct, but am having difficulty with the last section in bold. Any help would be greatly appreciated.

Thanks.

 

[Peter_SSs]

All the responses so far have been very interesting, and an education for someone who isn't great with arrays.

But do they all work? You will have seen some discussion among the helpers about certain functions being available or not available to some people. It would be useful to know whether they are available to you. Reason being, those people making suggestions involving, for example, the TAKE function may not wish to spend their time developing suggestions for your follow-up question(s) if you do not have access to that function.

You will also have seen suggestions just for Bolton and other suggestions for a list of teams from your table. It would be helpful to know which of those circumstances you actually want.

 

[ormy28]

Hi again. Apologies for the delay, an insanely busy week means I have only just been able to get back to this.

Thank you again for your replies. Although I didn't originally ask, the methods of listing each individual team are very useful and will save time.

I do have access to the TAKE functions, so I have been able to use these ok. All the suggestions from jasonb75, Eric W, cmowla and Peter_SSs work for the last 5 matches overall.

However, I perhaps should have been clearer in my original question... I need to see the number of matches and the number of goals scored in each individual teams last 5 matches. Therefore, Leicester would return 1 in column K and 1 in column L.

bebo021999 solution appears to do this. If there is a way to tweak this that would also show the number of goals conceded by each team in their last 5 matches, that would be great.

Thanks.

 

[jasonb75]

Would you have the entire league in one table then? I think the others have all based their suggestions on that assumption. I was working on the basis of the table having all games for a specific team in line with the example.

 

[cmowla]

It isn't pretty, but it gets the job done. (I thought it would be easiest to make a "new table" in columns N - P to start out with which helps to get the last n games for each team. From there, it was simply getting indexes of the new table!)

• Must carry down formulas in columns J - M.
• Allows you to change the # of games to see the stats for in H2.
• Ignore columns N - P, but the formula/table needs to be there!

Good Dynamic Array Fomrula Exercises.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Home Team	Away Team	Home Team 1st Half	Away Team 1st Half	Home Team 2nd Half	Away Team 2nd Half		Look at the last (input below) number of games.	Team	Last 5 Games (2nd Half)	Last 5 No of Goals (2nd Half)	Last 5 Games Conceded (2nd Half)	Last 5 No of Goals Conceded (2nd Half)	Team	2nd Half	Index
2	Bolton	Leicester	1	0	2	1		5	Bolton	2	4	3	4	Torquay	2	28
3	QPR	Bolton	1	1	0	1			Carlisle	2	6	2	3	Torquay	1	12
4	Bolton	Huddersfield	2	1	1	1			Exeter	1	1	1	3	Torquay	2	10
5	Newcastle	Bolton	0	1	0	0			Grimsby	0	0	0	0	QPR	1	18
6	Torquay	Bolton	1	1	2	1			Huddersfield	3	5	3	3	QPR	0	4
7	Torquay	Huddersfield	0	1	1	3			Leicester	3	4	3	5	Newcastle	1	26
8	Bolton	Grimsby	1	0	0	0			Newcastle	1	1	0	0	Newcastle	0	8
9	Bolton	Exeter	1	2	3	1			QPR	1	1	2	3	Leicester	2	32
10	QPR	Bolton	1	1	1	2			Torquay	3	5	3	7	Leicester	1	21
11	Bolton	Leicester	1	0	2	1								Leicester	1	3
12	Carlisle	Bolton	1	1	5	1								Huddersfield	1	25
13	Bolton	Huddersfield	2	1	1	1								Huddersfield	3	13
14	Newcastle	Bolton	0	1	1	0								Huddersfield	1	7
15	Torquay	Bolton	1	1	2	3								Grimsby	0	31
16	Bolton	Grimsby	1	0	0	0								Grimsby	0	15
17	Leicester	Carlisle	1	0	2	1								Exeter	0	35
18	Bolton	Exeter	1	2	0	0								Exeter	1	17
19														Carlisle	1	33
20														Carlisle	5	22
21														Bolton	0	34
22														Bolton	0	30
23														Bolton	3	29
24														Bolton	0	27
25														Bolton	1	24
26														Bolton	1	23
27														Bolton	2	20
28														Bolton	2	19
29														Bolton	3	16
30														Bolton	0	14
31														Bolton	1	11
32														Bolton	0	9
33														Bolton	1	6
34														Bolton	1	5
35														Bolton	2	2
Condensed 2

Cell Formulas
Range	Formula
J1	J1	="Last "&H2&" Games (2nd Half)"
K1	K1	="Last "&H2&" No of Goals (2nd Half)"
L1	L1	="Last "&H2&" Games Conceded (2nd Half)"
M1	M1	="Last "&H2&" No of Goals Conceded (2nd Half)"
I2:I10	I2	=UNIQUE(SORT(VSTACK(Table4[Home Team],Table4[Away Team])))
J2:J11	J2	=IF(I2<>"",SUM(IF(INDEX($N$2#,TAKE(TEXTSPLIT(TEXTJOIN(",",1,IF($N$2#=I2,SEQUENCE(ROWS($N$2#)),"")),","),,$H$2),2)<>0,1,0)),"")
K2:K11	K2	=IF(I2<>"",SUM(INDEX($N$2#,TAKE(TEXTSPLIT(TEXTJOIN(",",1,IF($N$2#=I2,SEQUENCE(ROWS($N$2#)),"")),","),,$H$2),2)),"")
L2:L11	L2	=IF(I2<>"",LET(i,INDEX($N$2#,TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,TAKE(TEXTSPLIT(TEXTJOIN(",",,IF($N$2#=I2,SEQUENCE(ROWS($N$2#)),"")),","),,$H$2)),",")),3),SUM(IF(INDEX($N$2#,TEXTSPLIT(TEXTJOIN(",",,IF(INDEX($N$2#,,3)=TRANSPOSE(IF(MOD(i,2)=0,i+1,i-1)),SEQUENCE(ROWS(INDEX($N$2#,,1))),"")),","),2)>0,1,0))),"")
M2:M11	M2	=IF(I2<>"",LET(i,INDEX($N$2#,TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,TAKE(TEXTSPLIT(TEXTJOIN(",",,IF($N$2#=I2,SEQUENCE(ROWS($N$2#)),"")),","),,$H$2)),",")),3),SUM(INDEX($N$2#,TEXTSPLIT(TEXTJOIN(",",,IF(INDEX($N$2#,,3)=TRANSPOSE(IF(MOD(i,2)=0,i+1,i-1)),SEQUENCE(ROWS(INDEX($N$2#,,1))),"")),","),2))),"")
N2:P35	N2	=LET(ht,Table4[Home Team],SORT(HSTACK(VSTACK(ht,Table4[Away Team]),VSTACK(Table4[Home Team 2nd Half],Table4[Away Team 2nd Half]),VSTACK(2*(ROW(ht)-1),2*(ROW(ht)-1)+1)),{1,3},-1))
Dynamic array formulas.

 

[Peter_SSs]

See if these do what you want (check the table name)
In column M - Goals conceded in last (up to) 5 games - I have used a different notation which makes the formula considerably shorter, but perhaps harder to read/understand. If it does what you want but wanted the fuller version, M2 would be

=LET(z,IF(Table5[Home Team]=J2,Table5[Away Team 1stHalf]+Table5[Away Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Home Team 1stHalf]+Table5[Home Team 2nd Half],"")),SUM(TAKE(FILTER(z,z<>""),-5)))

ormy28.xlsm
	A	B	C	D	E	F			I	J	K	L	M
1	Home Team	Away Team	Home Team 1stHalf	Away Team 1stHalf	Home Team 2nd Half	Away Team 2nd Half				Team	Last 5: No of Games (2ndH)	Last 5: No of Goals (2ndH)	Last 5: Goals Conceded
2	Bolton	Leicester	1	0	2	1				Bolton	3	7	6
3	Carlisle	Bolton	1	1	0	1				Carlisle	0	0	2
4	Bolton	Huddersfield	2	1	1	1				Exeter	1	1	4
5	Newcastle	Bolton	0	1	0	0				Grimsby	0	0	1
6	Torquay	Bolton	1	1	0	2				Huddersfield	1	1	3
7	Bolton	Grimsby	1	0	0	0				Leicester	1	1	3
8	Bolton	Exeter	1	2	3	1				Newcastle	0	0	1
9	QPR	Bolton	1	1	1	2				QPR	1	1	3
10										Torquay	0	0	3
Sheet3

Cell Formulas
Range	Formula
J2:J10	J2	=SORT(UNIQUE(VSTACK(Table5[Home Team],Table5[Away Team])))
K2:K10	K2	=LET(z,IF(Table5[Home Team]=J2,Table5[Home Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Away Team 2nd Half],"")),y,TAKE(FILTER(z,z<>""),-5),COUNT(FILTER(y,y>0)))
L2:L10	L2	=LET(z,IF(Table5[Home Team]=J2,Table5[Home Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Away Team 2nd Half],"")),SUM(TAKE(FILTER(z,z<>""),-5)))
M2:M10	M2	=LET(t,Table5,z,IF(INDEX(t,0,1)=J2,INDEX(t,0,4)+INDEX(t,0,6),IF(INDEX(t,0,2)=J2,INDEX(t,0,3)+INDEX(t,0,5),"")),SUM(TAKE(FILTER(z,z<>""),-5)))
Dynamic array formulas.

 

[ormy28]

See if these do what you want (check the table name)
In column M - Goals conceded in last (up to) 5 games - I have used a different notation which makes the formula considerably shorter, but perhaps harder to read/understand. If it does what you want but wanted the fuller version, M2 would be

=LET(z,IF(Table5[Home Team]=J2,Table5[Away Team 1stHalf]+Table5[Away Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Home Team 1stHalf]+Table5[Home Team 2nd Half],"")),SUM(TAKE(FILTER(z,z<>""),-5)))

ormy28.xlsm
	A	B	C	D	E	F			I	J	K	L	M
1	Home Team	Away Team	Home Team 1stHalf	Away Team 1stHalf	Home Team 2nd Half	Away Team 2nd Half				Team	Last 5: No of Games (2ndH)	Last 5: No of Goals (2ndH)	Last 5: Goals Conceded
2	Bolton	Leicester	1	0	2	1				Bolton	3	7	6
3	Carlisle	Bolton	1	1	0	1				Carlisle	0	0	2
4	Bolton	Huddersfield	2	1	1	1				Exeter	1	1	4
5	Newcastle	Bolton	0	1	0	0				Grimsby	0	0	1
6	Torquay	Bolton	1	1	0	2				Huddersfield	1	1	3
7	Bolton	Grimsby	1	0	0	0				Leicester	1	1	3
8	Bolton	Exeter	1	2	3	1				Newcastle	0	0	1
9	QPR	Bolton	1	1	1	2				QPR	1	1	3
10										Torquay	0	0	3
Sheet3

Cell Formulas
Range	Formula
J2:J10	J2	=SORT(UNIQUE(VSTACK(Table5[Home Team],Table5[Away Team])))
K2:K10	K2	=LET(z,IF(Table5[Home Team]=J2,Table5[Home Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Away Team 2nd Half],"")),y,TAKE(FILTER(z,z<>""),-5),COUNT(FILTER(y,y>0)))
L2:L10	L2	=LET(z,IF(Table5[Home Team]=J2,Table5[Home Team 2nd Half],IF(Table5[Away Team]=J2,Table5[Away Team 2nd Half],"")),SUM(TAKE(FILTER(z,z<>""),-5)))
M2:M10	M2	=LET(t,Table5,z,IF(INDEX(t,0,1)=J2,INDEX(t,0,4)+INDEX(t,0,6),IF(INDEX(t,0,2)=J2,INDEX(t,0,3)+INDEX(t,0,5),"")),SUM(TAKE(FILTER(z,z<>""),-5)))
Dynamic array formulas.

Works a treat that. Thanks.

And thank you all for your help. Using the TAKE and LET functions will be something I will look into using more frequently in future.

 

[Peter_SSs]

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