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.

 

[jasonb75]

I'm not normally a fan of OFFSET due to it being a volatile function, but I think that with what you're trying to do it may well be the lesser of evils.

Book1
	J	K	L
1	Team	Last 5 No of Games (2ndH)	Last 5 No of Goals (2ndH)
2	Bolton	3	7
Sheet1

Cell Formulas
Range	Formula
K2	K2	=COUNTIFS(OFFSET(Table4[[#Headers],[Home Team]],ROWS(Table4),,-5,2),J2,OFFSET(Table4[[#Headers],[Home 2nd Half Y/N]],ROWS(Table4),,-5,2),"Y")
L2	L2	=SUMIFS(OFFSET(Table4[[#Headers],[Home Team 2nd Half]],ROWS(Table4),,-5,2),OFFSET(Table4[[#Headers],[Home Team]],ROWS(Table4),,-5,2),J2)

 

[Eric W]

A way without OFFSET:

Book1
	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
10
Sheet1

Cell Formulas
Range	Formula
G2:H9	G2	=IF(E2>0,"Y","N")
K2	K2	=LET(r,ROWS(Table4),t,FILTER(Table4[[Home Team]:[Away Team 2nd Half]],SEQUENCE(r)>r-5),SUM((FILTER(t,{1,1,0,0,0,0})=J2)*(FILTER(t,{0,0,0,0,1,1})>0)))
L2	L2	=LET(r,ROWS(Table4),t,FILTER(Table4[[Home Team]:[Away Team 2nd Half]],SEQUENCE(r)>r-5),SUM((FILTER(t,{1,1,0,0,0,0})=J2)*(FILTER(t,{0,0,0,0,1,1}))))

 

[cmowla]

I was literally working on this since the thread was posted!

First of all, according to the heading of Column K (in the original post), shouldn't the value should be 5, not 3?

With this option (which it's 5 and not 3),

• Column J will automatically list all of the teams that played. (I Used/modified this general formula to combine 2 arrays Is there a way to concatenate two arrays in Excel without VBA?). (@Eric W , do you know of a better way to do that? I was thinking to combine TEXTSPLIT() with CONCAT(), but I don't have TEXTSPLIT on my current installation of 365. Maybe I need to update?)
• Not that the other options aren't like this, but the formulas I used will not be broken if the table is offset.
• Clearly I have several helper cells.
• Clearly I used the hideous volatile function, INDIRECT. But using it made the formulas in Column K and Column L pretty simple in structure.
• All in all, I am not implying that my solution should be considered (but the calculation for column K should be fixed if it's indeed 5 and not 3 by future posters!), but this was a fun exercise!

Blank.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Home Team	Away Team	Home Team 1st Half	Away Team 1st Half	Home Team 2nd Half	Away Team 2nd Half	Home 2nd Half Y/N	Away 2nd Half Y/N		Team	Last 5 No of Games (2nd Half)	Last 5 No of Goals (2nd Half)		First Row Number	Last Row Number	Home Team Column Letter	Away Team Column Letter	Home Team 2nd Half Column Letter	Away Team 2nd Half Column Letter	Home Teams' Range	Away Teams' Range	Entire Home Team's Column Range
2	Bolton	Leicester	1	0	2	1	Y	Y		Bolton	5	7		2	9	A	B	E	F	A2:A9	B2:B9	$A:$A
3	Carlisle	Bolton	1	1	0	1	N	Y		Carlisle	0	0
4	Bolton	Huddersfield	2	1	1	1	Y	Y		Newcastle	1	0
5	Newcastle	Bolton	0	1	0	0	N	N		Torquay	1	0
6	Torquay	Bolton	1	1	0	2	N	Y		QPR	1	1
7	Bolton	Grimsby	1	0	0	0	N	N		Leicester	0	0
8	Bolton	Exeter	1	2	3	1	Y	Y		Huddersfield	0	0
9	QPR	Bolton	1	1	1	2	Y	Y		Grimsby	1	0
10										Exeter	1	1
Sheet9

Cell Formulas
Range	Formula
J2:J10	J2	=UNIQUE(INDEX(CHOOSE({1,2},INDIRECT(T2),INDIRECT(U2)),N(IF({1},ROW(INDEX(INDIRECT(V2),1):INDEX(INDIRECT(V2),2*O2-2))-IF(ROW(INDEX(INDIRECT(V2),1):INDEX(INDIRECT(V2),2*O2-2))<=O2-1,0,O2-1))),N(IF({1},2-(ROW(INDEX(INDIRECT(V2),1):INDEX(INDIRECT(V2),2*O2-2))<=O2-1)))))
K2:K10	K2	=SUM(IF(INDIRECT($P$2&$O$2-4&":"&$Q$2&$O$2)=J2,1,0))
L2:L10	L2	=SUM(INDIRECT($R$2&$O$2-4&":"&$S$2&$O$2)*IF(INDIRECT($P$2&$O$2-4&":"&$Q$2&$O$2)=J2,1,0))
N2	N2	=MIN(ROW(Table4[Home Team]))-ROW(N1)+1
O2	O2	=MIN(ROW(Table4))+ROWS(Table4)-ROW(N1)
P2	P2	=SUBSTITUTE(ADDRESS(1,COLUMN(Table4[[#Headers],[Home Team]]),4),"1","")
Q2	Q2	=SUBSTITUTE(ADDRESS(1,COLUMN(Table4[[#Headers],[Away Team]]),4),"1","")
R2	R2	=SUBSTITUTE(ADDRESS(1,COLUMN(Table4[[#Headers],[Home Team 2nd Half]]),4),"1","")
S2	S2	=SUBSTITUTE(ADDRESS(1,COLUMN(Table4[[#Headers],[Away Team 2nd Half]]),4),"1","")
T2	T2	=P2&N2&":"&P2&O2
U2	U2	=Q2&N2&":"&Q2&O2
V2	V2	="$"&P2&":"&"$"&P2
Dynamic array formulas.

 

[jasonb75]

shouldn't the value should be 5, not 3?

Sorry, but definitely 3, going from the bottom, up it's the last 5 rows where the criteria team has a Y in the corresponding cell as I've highlighted in the min sheet below.

Book1
	A	B	C	D	E	F	G	H
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
Sheet1

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

A way without OFFSET:

I did start off with LET and INDEX unions in an attempt to avoid volatile functions, but went with OFFSET for simplicity.

 

[bebo021999]

What if a team has less than 5 matches? Count or not count?
I came up with 2 options: Count and not count

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
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)	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	7
3	Carlisle	Bolton	1	1	0	1	N	Y		Carlisle	Less than 5 matches	Less than 5 matches	1	1
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	Leicester	Carlisle	1	0	2	1	Y	Y			OPTION 1		OPTION 2
9	Bolton	Exeter	1	2	3	1	Y	Y
10	QPR	Bolton	1	1	1	2	Y	Y
Sheet1

Cell Formulas
Range	Formula
K2:K3	K2	=IFERROR(SUMPRODUCT((($A$2:$B$10=$J2)*($E$2:$F$10>0))*(ROW($A$2:$A$10)>=AGGREGATE(14,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),5))),"Less than 5 matches")
L2:L3	L2	=IFERROR(SUMPRODUCT((($A$2:$B$10=$J2)*$E$2:$F$10)*(ROW($A$2:$A$10)>=AGGREGATE(14,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),5))),"Less than 5 matches")
M2:M3	M2	=SUMPRODUCT((($A$2:$B$10=$J2)*($E$2:$F$10>0))*(ROW($A$2:$A$10)>=IFERROR(AGGREGATE(14,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),5),AGGREGATE(15,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),1))))
N2:N3	N2	=SUMPRODUCT((($A$2:$B$10=$J2)*$E$2:$F$10)*(ROW($A$2:$A$10)>=IFERROR(AGGREGATE(14,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),5),AGGREGATE(15,6,ROW($A$2:$A$10)/(($A$2:$A$10=$J2)+($B$2:$B$10=$J2)),1))))
A8	A8	=B2
B8	B8	=A3

 

[cmowla]

Okay, here's another approach (to the original question).

Notes:

• I renamed some of the column headers, and thus this formula won't work unless you change either mine to yours or yours to mine.
• I condensed the UNIQUE formula and eliminated all helper cells.
• The formulas for Columns K and L are just a "product" (actually a concatenation) of 2D arrays. So just observe what they are separately, then "mulltiply" them together, and then apply the outer conditionals to get the result.
• They don't use any volatile functions and only use the SEQUENCE function (with of course conditionals).

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

Cell Formulas
Range	Formula
J2:J10	J2	=SORT(UNIQUE(INDEX(CHOOSE({1,2},Table4[Home Team],Table4[Away Team]),N(IF({1},SEQUENCE(2*ROWS(Table4),1,1)-IF(SEQUENCE(2*ROWS(Table4),1,1)<=ROWS(Table4[Home Team]),0,ROWS(Table4[Home Team])))),N(IF({1},2-(SEQUENCE(2*ROWS(Table4),1,1)<=ROWS(Table4[Home Team])))))))
K2:K10	K2	=SUM(IF(Table4[[Home Team]:[Away Team]]&IF(SEQUENCE(ROWS(Table4),2,1,1)>2*(ROWS(Table4))-10,"",0)&IF(Table4[[Home 2nd Half Y/N]:[Away 2nd Half Y/N]]="Y","",0)=J2,1,0))
L2:L10	L2	=SUM(IF(Table4[[Home Team]:[Away Team]]&IF(SEQUENCE(ROWS(Table4),2,1,1)>2*(ROWS(Table4))-10,"",0)=J2,1,0)*Table4[[Home Team 2nd Half]:[Away Team 2nd Half]])
Dynamic array formulas.

 

[Peter_SSs]

I am assuming that..

• We are looking at the last 5 rows of the table, not necessarily the last 5 matches played by a team (based on the thread title)
• The table has at least 5 rows of data
• That you have the latest dynamic array functions. That may not be the case as some have only been rolled out to a limited number of users so far, but you should get them eventually.

Columns J:L if you are just looking at a particular team.
Columns N:P if you want results for all teams that appear in the last 5 rows.
None of the formulas need to be copied down.

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

Cell Formulas
Range	Formula
K2	K2	=LET(t,TAKE(Table4,-5),COUNTIFS(INDEX(t,0,1),J2,INDEX(t,0,5),">0")+COUNTIFS(INDEX(t,0,2),J2,INDEX(t,0,6),">0"))
L2	L2	=LET(t,TAKE(Table4,-5),SUMIF(INDEX(t,0,1),J2,INDEX(t,0,5))+SUMIF(INDEX(t,0,2),J2,INDEX(t,0,6)))
N2:N7	N2	=SORT(UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,TAKE(Table4[[Home Team]:[Away Team]],-5)),","))))
O2:O7	O2	=LET(t,TAKE(Table4,-5),COUNTIFS(INDEX(t,0,1),N2#,INDEX(t,0,5),">0")+COUNTIFS(INDEX(t,0,2),N2#,INDEX(t,0,6),">0"))
P2:P7	P2	=LET(t,TAKE(Table4,-5),SUMIF(INDEX(t,0,1),N2#,INDEX(t,0,5))+SUMIF(INDEX(t,0,2),N2#,INDEX(t,0,6)))
Dynamic array formulas.

 

[cmowla]

TAKE(Table4,-5)

Wow, I didn't know of that one. Using this, my formulas are shorter and more understandible now. Thanks man!

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

Cell Formulas
Range	Formula
J2:J7	J2	=SORT(UNIQUE(TRANSPOSE(TEXTSPLIT(SUBSTITUTE(CONCAT(TAKE(Table4[[Home Team]:[Away Team]],-5)&",")&",",",,",""),","))))
K2:K9	K2	=IF(J2="","",SUM(TAKE(IF(Table4[[Home Team]:[Away Team]]&IF(Table4[[Home 2nd Half Y/N]:[Away 2nd Half Y/N]]="Y","",0)=J2,1,0),-5)))
L2:L9	L2	=IF(J2="","",SUM(TAKE(IF(Table4[[Home Team]:[Away Team]]=J2,1,0)*Table4[[Home Team 2nd Half]:[Away Team 2nd Half]],-5)))
Dynamic array formulas.

 

[Peter_SSs]

my formulas are shorter and more understandible now.

True, but they still require the helper columns G & H (with formulas), which mine don't
.. and they also require copying down, which mine don't