Most recent 6 values

[RedPed]

I have some football data where I have data for completed matches and also upcoming fixtures.

I would like to show the the number of goals scored by each team form their most recent 6 matches played (note that this will include both home and away matches played by the teams). example screenshot of completed matches below and an example of the upcoming fixtures. Is this possible or do I need to think of another solution?

 

[RobP]

Hi I see nobody had a go at this one, presumably as your profile says Excel 2016. So I took the challenge to see if I could make it work with O365, in the event you might upgrade at some point.
It pretty much does what you want, except I get a #value error when a team has played only home or away game (ie. team name exists in only 1 column. Over a season that would correct itself, but might show up say for example a new team just promoted, has played only his first game. It would self fix for the next match.
I tried to get an error capture for it, but failed .. no doubt someone here could help more I imagine.

Anyway, 2 formulas, one for home team goals, one for away team goals. The yellow cells were just for me to easily see Brentfords goals whilst building the formula.

cheers
Rob

football scores.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Home Team	Away Team	Home Team Goals	Away Team Goals			HT Goals 1	HT Goals 2	HT Goals 3	HT Goals 4	HT Goals 5	HT Goals 6	AT Goals 1	AT Goals 2	AT Goals 3	AT Goals 4	AT Goals 5	AT Goals 6
2	13/08/2021	Brentford	Arsenal	2	0			0	0	5	1	2	0	0	0
3	14/08/2021	Norwich	Liverpool	0	3			0	0					2	3
4	14/08/2021	Everton	Southampton	3	1			2	3					1	1
5	14/08/2021	Chelsea	Brentford	3	0			2	3					0	0	5	1	2	0
6	14/08/2021	Man Utd	Leeds	5	1			1	5					2	1
7	14/08/2021	Watford	Aston Villa	3	2			0	3					2	2
8	15/08/2021	Burnley	Brentford	1	2			0	1					0	0	5	1	2	0
9	14/08/2021	Leicester	Wolves	1	0			#VALUE!						#VALUE!
10	16/08/2021	Brentford	Man City	1	0			0	0	5	1	2	0	#VALUE!
11	15/08/2021	Newcastle	West Ham	2	4			0	2					#VALUE!
12	21/08/2021	Liverpool	Burnley	2	0			2	3					0	1
13	21/08/2021	Aston Villa	Newcastle	2	0			2	2					0	2
14	21/08/2021	Brentford	Norwich	5	0			0	0	5	1	2	0	0	0
15	23/08/2021	C Palace	Brentford	0	0			#VALUE!						0	0	5	1	2	0
16	21/08/2021	Leeds	Everton	2	2			2	1					2	3
17	21/08/2021	Brighton	Watford	2	0			#VALUE!						0	3
18	22/08/2021	Southampton	Man Utd	1	1			1	1					1	5
19	22/08/2021	Arsenal	Chelsea	0	2			0	0					2	3
20	24/08/2021	Brentford	Tottenham	0	1			0	0	5	1	2	0	#VALUE!
Sheet1

Cell Formulas
Range	Formula
H2:M2,H20:M20,H18:I19,H16:I16,H14:M14,H11:I13,H10:M10,H9,H15,H17,H3:I8	H2	=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$20,$B$2:$B$20=$B2,"no team"),1,2,4),CHOOSECOLS(FILTER($A$2:$E$20,$C$2:$C$20=$B2,"no team"),1,3,5)),1,-1),3),IF(ROWS(scores)>6,CHOOSEROWS(scores,1,2,3,4,5,6),scores)))
N16:O19,N15:S15,N12:O14,N9:N11,N20,N8:S8,N6:O7,N5:S5,N2:O4	N2	=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$20,$B$2:$B$20=$C2,"no team"),1,2,4),CHOOSECOLS(FILTER($A$2:$E$20,$C$2:$C$20=$C2,"no team"),1,3,5)),1,-1),3),IF(ROWS(scores)>6,CHOOSEROWS(scores,1,2,3,4,5,6),scores)))
Dynamic array formulas.

 

[Fluff]

@RobP The OP's profile clearly shows 2016 & so does not have most of those functions as they only exist in 365

 

[Peter_SSs]

@RobP The OP's profile clearly shows 2016 & so does not have most of those functions as they only exist in 365

To be fair, @RobP did at least acknowledge that

as your profile says Excel 2016. So I took the challenge to see if I could make it work with O365, in the event you might upgrade at some point.

I see nobody had a go at this one, presumably as your profile says Excel 2016.

I had not seen this one so I'll have a go at it now for 2016. I have assumed that in the bottom right image in post 1 that 'HT Goals 1' means the goals scored by the Home team in that bottom right image in the latest game played. That is Liverpool goals in their last match. 'HT Goals 2' would be Liverpool's goals in their second last match etc.
Manual colour coding was also just for my benefit in checking.

23 08 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	Home Team	Away Team	Home Team Goals	Away Team Goals					HT Goals 1	HT Goals 2	HT Goals 3	HT Goals 4	HT Goals 5	HT Goals 6	AT Goals 1	AT Goals 2	AT Goals 3	AT Goals 4	AT Goals 5	AT Goals 6
2	13/08/2021	Brentford	Arsenal	2	0			Liverpool	Arsenal	2	3					0	0
3	14/08/2021	Norwich	Liverpool	0	3			Leeds	Wolves	2	1					0
4	14/08/2021	Everton	Southampton	3	1
5	14/08/2021	Chelsea	Brentford	3	0
6	14/08/2021	Man Utd	Leeds	5	1
7	14/08/2021	Watford	Aston Villa	3	2
8	15/08/2021	Burnley	Brentford	1	2
9	14/08/2021	Leicester	Wolves	1	0
10	16/08/2021	Brentford	Man City	1	0
11	15/08/2021	Newcastle	West Ham	2	4
12	21/08/2021	Liverpool	Burnley	2	0
13	21/08/2021	Aston Villa	Newcastle	2	0
14	21/08/2021	Brentford	Norwich	5	0
15	23/08/2021	C Palace	Brentford	0	0
16	21/08/2021	Leeds	Everton	2	2
17	21/08/2021	Brighton	Watford	2	0
18	22/08/2021	Southampton	Man Utd	1	1
19	22/08/2021	Arsenal	Chelsea	0	2
20	24/08/2021	Brentford	Tottenham	0	1
Goals in last 6

Cell Formulas
Range	Formula
J2:O3	J2	=IFERROR(INDEX($D:$E,AGGREGATE(14,6,ROW($D$2:$E$20)/(($B$2:$B$20=$H2)+($C$2:$C$20=$H2)),COLUMNS($J:J)),1+(INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$20)/(($B$2:$B$20=$H2)+($C$2:$C$20=$H2)),COLUMNS($J:J)))=$H2)),"")
P2:U3	P2	=IFERROR(INDEX($D:$E,AGGREGATE(14,6,ROW($D$2:$E$20)/(($B$2:$B$20=$I2)+($C$2:$C$20=$I2)),COLUMNS($P:P)),1+(INDEX($C:$C,AGGREGATE(14,6,ROW($B$2:$B$20)/(($B$2:$B$20=$I2)+($C$2:$C$20=$I2)),COLUMNS($P:P)))=$I2)),"")

 

[Fluff]

To be fair, @RobP did at least acknowledge that

Good point, I missed that completely.
Apologies to @RobP

 

[RobP]

No worries @Fluff & thanks @Peter_SSs. at least he has his answer now - but I don't have mine Any idea how to trap that #value issue I was getting because of the "filter" command .. ?

Just as I couldn't find a way to wrap it myself.

cheers
Rob

 

[Peter_SSs]

but I don't have mine Any idea how to trap that #value issue I was getting

Just wrap your whole formula in an IFERROR?

=IFERROR(your_formula,"")

 

[RobP]

Hi Pete,

if only it were that simple ... If I wrap it all up, and say just insert the home team goal (as theres only going to be one) instead - it works. BUT, when the home team has played 2 home games only it inserting wrong data. So not quite there.

I was trying to wrap it up around the filter commands inside, but was getting true/false array data back instead of my results .. so failed miserably.

Anyway, glad its not just me ...

cheers
Rob

 

[Peter_SSs]

if only it were that simple ...

To be honest, I hadn't studied your formula in detail so suggested what seemed likely, but obviously wasn't.

What about this then?

23 08 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Home Team	Away Team	Home Team Goals	Away Team Goals			HT Goals 1	HT Goals 2	HT Goals 3	HT Goals 4	HT Goals 5	HT Goals 6	AT Goals 1	AT Goals 2	AT Goals 3	AT Goals 4	AT Goals 5	AT Goals 6
2	18/08/2021	Brentford	Arsenal	2	0			0	2					0
3	19/08/2021	A	Z	5	7			2	5					1	2	3	4	5	6
4	20/08/2021	C Palace	Z	0	6			0						1	2	3	4	5	6
5	21/08/2021	A	Everton	2	2			2	5					2
6	22/08/2021	B	Z	1	5			1						1	2	3	4	5	6
7	23/08/2021	C	Z	2	4			2						1	2	3	4	5	6
8	24/08/2021	D	Z	3	3			3						1	2	3	4	5	6
9	25/08/2021	E	Z	4	2			4						1	2	3	4	5	6
10	26/08/2021	Brentford	Z	0	1			0	2					1	2	3	4	5	6
Goals in last 6 (2)

Cell Formulas
Range	Formula
H10:I10,H5:I5,H4,H6:H9,H2:I3	H2	=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$10,$B$2:$B$10=$B2,{"","","",""}),1,2,4),CHOOSECOLS(FILTER($A$2:$E$10,$C$2:$C$10=$B2,{"","","","",""}),1,3,5)),1,-1),3),scores2,FILTER(scores,scores<>""),IF(ROWS(scores2)>6,CHOOSEROWS(scores2,1,2,3,4,5,6),scores2)))
N2,N5,N6:S10,N3:S4	N2	=TRANSPOSE(LET(scores,CHOOSECOLS(SORT(VSTACK(CHOOSECOLS(FILTER($A$2:$E$10,$B$2:$B$10=$C2,{"","","",""}),1,2,4),CHOOSECOLS(FILTER($A$2:$E$10,$C$2:$C$10=$C2,{"","","","",""}),1,3,5)),1,-1),3),scores2,FILTER(scores,scores<>""),IF(ROWS(scores2)>6,CHOOSEROWS(scores2,1,2,3,4,5,6),scores2)))
Dynamic array formulas.

Here is another 365 alternative to consider though:

23 08 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Date	Home Team	Away Team	Home Team Goals	Away Team Goals			HT Goals 1	HT Goals 2	HT Goals 3	HT Goals 4	HT Goals 5	HT Goals 6	AT Goals 1	AT Goals 2	AT Goals 3	AT Goals 4	AT Goals 5	AT Goals 6
2	18/08/2021	Brentford	Arsenal	2	0			0	2					0
3	19/08/2021	A	Z	5	7			2	5					1	2	3	4	5	6
4	20/08/2021	C Palace	Z	0	6			0						1	2	3	4	5	6
5	21/08/2021	A	Everton	2	2			2	5					2
6	22/08/2021	B	Z	1	5			1						1	2	3	4	5	6
7	23/08/2021	C	Z	2	4			2						1	2	3	4	5	6
8	24/08/2021	D	Z	3	3			3						1	2	3	4	5	6
9	25/08/2021	E	Z	4	2			4						1	2	3	4	5	6
10	26/08/2021	Brentford	Z	0	1			0	2					1	2	3	4	5	6
Goals in last 6 (3)

Cell Formulas
Range	Formula
H10:I10,H5:I5,H4,H6:H9,H2:I3	H2	=TAKE(TOROW(IF(SORTBY(B$2:C$10,A$2:A$10,-1)=B2,SORTBY(D$2:E$10,A$2:A$10,-1),1/0),2),,6)
N2,N5,N6:S10,N3:S4	N2	=TAKE(TOROW(IF(SORTBY(B$2:C$10,A$2:A$10,-1)=C2,SORTBY(D$2:E$10,A$2:A$10,-1),1/0),2),,6)
Dynamic array formulas.

 

[RobP]

Thanks @Peter_SSs - its ok, you're not the first on here to not read all the details before writing an answer . (Myself included).

Works great as I had planned (even if not quite what the OP had in mind - your Xl2016 fixed all that though). I can't find any documentation to explain what those curly braces and "" are doing in the FILTER - but will keep looking. I prefer your last suggestion though, as a) looks much cleaner, and b) its a new take and new stuff for me to learn instead of my goto FILTER .... I'm off to figure it out now..

thanks a lot for the learning as usual.
Rob

sorry, I take it back the 2nd example you gave doesn't like the Away Team goals for some reason where 1 instance of the team exists.