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]

which mine don't

True, but they do use functions that very few people will have available at the moment.

While I'm here, I might as well throw another alternative into the mix...

Book2
	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
Sheet3

Cell Formulas
Range	Formula
G2:H9	G2	=IF(E2>0,"Y","N")
K2	K2	=LET(r,ROWS(Table4)-4,s,SEQUENCE(5,,r),SUM((INDEX(Table4,s,{1,2})=J2)*(INDEX(Table4,s,{5,6})>0)))
L2	L2	=LET(r,ROWS(Table4)-4,s,SEQUENCE(5,,r),SUM((INDEX(Table4,s,{1,2})=J2)*(INDEX(Table4,s,{5,6}))))

 

[cmowla]

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

I'm glad you mentioned that. Me modifying the first formula to not use the result of the formula in the (old) columns G & H made it shorter and even more understandible!

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

Cell Formulas
Range	Formula
H2:H7	H2	=SORT(UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,TAKE(Table4[[Home Team]:[Away Team]],-5)),","))))
I2:I9	I2	=IF(H2="","",SUM(TAKE(IF(IF(Table4[[Home Team 2nd Half]:[Away Team 2nd Half]]>0,Table4[[Home Team]:[Away Team]])=H2,1,0),-5)))
J2:J9	J2	=IF(H2="","",SUM(TAKE(IF(Table4[[Home Team]:[Away Team]]=H2,1,0)*Table4[[Home Team 2nd Half]:[Away Team 2nd Half]],-5)))
Dynamic array formulas.

.. and they also require copying down, which mine don't

Well, that's pretty cool. (I noticed that the first time you mentioned it.) But I guess to each his own preference. The time one spends copying down simple formulas down to row 10,000 (just once, which should be easy, especially if they know the "select the cells first, then press Ctrl D" trick) compared to the difference in time it will take one to understand your formulas over the more simple ones should be taken into account also. That is, if we are going to put any weight on the advantage to not copy down as truly being a time saver.

 

[Peter_SSs]

but they do use functions that very few people will have available at the moment.

I thought that I had covered that off with ..

.. 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.

.. and who is to know if the OP might be one of those few people? Even if not, could be useful for future readers when the functions are more widely available.

The time one spends copying down simple formulas down to row 10,000 (just once, which should be easy, especially if they know the "select the cells first, then press Ctrl D" trick) compared to the difference in time it will take one to understand your formulas over the more simple ones should be taken into account also. That is, if we are going to put any weight on the advantage to not copy down as truly being a time saver.

I wasn't suggesting that it was a time-saving issue, rather that the results will automatically spill to the correct number of rows without the user needing to determine what the maximum number of rows might ever be. There are also possible negative printing issues if formulas returning null strings are copied down past where the visible data actually ends. Still, as you said "I guess to each his own preference."

Regarding what formulas are simple and ease of understanding, I don't see a lot of difference between a SUM(IF(.. construct and SUMIF (or COUNTIFS)

 

[ormy28]

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

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

In the above solution, is there also a way to tweak the formulas for columns K and L to count the number of times and number of second half goals conceded over the last 5 games?.

Thanks.

 

[jasonb75]

I have't looked at bebo's suggestion, but with my last one it is as simple as swapping the last pair of index column numbers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
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 Goals Conceded (2ndH)
2	Bolton	Leicester	1	0	2	1	Y	Y		Bolton	3	7	2
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
Sheet3

Cell Formulas
Range	Formula
G2:H9	G2	=IF(E2>0,"Y","N")
K2	K2	=LET(r,ROWS(Table4)-4,s,SEQUENCE(5,,r),SUM((INDEX(Table4,s,{1,2})=J2)*(INDEX(Table4,s,{5,6})>0)))
L2	L2	=LET(r,ROWS(Table4)-4,s,SEQUENCE(5,,r),SUM((INDEX(Table4,s,{1,2})=J2)*(INDEX(Table4,s,{5,6}))))
M2	M2	=LET(r,ROWS(Table4)-4,s,SEQUENCE(5,,r),SUM((INDEX(Table4,s,{1,2})=J2)*(INDEX(Table4,s,{6,5}))))

 

[cmowla]

[...] is there also a way to tweak the formulas for columns K and L to count the number of times and number of second half goals conceded over the last 5 games?.

Here is my approach to all calculations (you can press Alt Enter to enter a new line in a cell formula):

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

Cell Formulas
Range	Formula
H2:H7	H2	=SORT(UNIQUE(VSTACK(TAKE(Table4[Home Team],-5),TAKE(Table4[Away Team],-5))))
I2:I9	I2	=IF(H2="","", IF(SUM(IF(TAKE(Table4[[Home Team]:[Away Team]],-5)=H2,1,0))<5,"Yes","") )
J2:J9	J2	=IF(H2="","", SUM(TAKE(IF(IF(Table4[[Home Team 2nd Half]:[Away Team 2nd Half]]>0,Table4[[Home Team]:[Away Team]])=H2,1,0),-5)) )
K2:K9	K2	=IF(H2="","", SUM(TAKE(IF(Table4[[Home Team]:[Away Team]]=H2,1,0)*Table4[[Home Team 2nd Half]:[Away Team 2nd Half]],-5)) )
L2:L9	L2	=IF(H2="","", SUM( TAKE( IF(Table4[Home Team]=H2,IF(Table4[Away Team 2nd Half]>0,1,0),0)+ IF(Table4[Away Team]=H2,IF(Table4[Home Team 2nd Half]>0,1,0),0), -5) ) )
M2:M9	M2	=IF(H2="","", SUM( TAKE(IF(Table4[Home Team]=H2,1,0)*Table4[Away Team 2nd Half],-5)+ TAKE(IF(Table4[Away Team]=H2,1,0)*Table4[Home Team 2nd Half],-5) ) )
Dynamic array formulas.

@Peter_SSs , I also discovered a new dynamic array function that shortened the SORT(UNIQUE()) list! I'm definitely going to use that (and the horizontal version) to join arrays from now on!

 

[cmowla]

If you don't want to have to carry the formula down, want to be able to change the last X number of games viewed (Cell N2), and were perhaps curious to see that all done in one formula,

(This is the first time I have played with the LET function. Crazy.)

(P.S., I couldn't do concatenation with arguments in an array {"text 1","text2",...}, so if you change the value of N2, you will have to manually change the 5's in the formula to the number it was changed to so that the headings say the correct thing.)

Blank3.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Home Team	Away Team	Home Team 1st Half	Away Team 1st Half	Home Team 2nd Half	Away Team 2nd Half		Team	Less than 5 Matches?	Last 5 No of Games (2nd Half)	Last 5 No of Goals (2nd Half)	Last 5 No of Games Conceded (2nd Half)	Last 5 No of Goals Conceded (2nd Half)	Look at the last (input below) number of games.
2	Bolton	Leicester	1	0	2	1		Bolton		3	7	2	2	5
3	Carlisle	Bolton	1	1	0	1		Exeter	Yes	1	1	1	3
4	Bolton	Huddersfield	2	1	1	1		Grimsby	Yes	0	0	0	0
5	Newcastle	Bolton	0	1	0	0		Newcastle	Yes	0	0	0	0
6	Torquay	Bolton	1	1	0	2		QPR	Yes	1	1	1	2
7	Bolton	Grimsby	1	0	0	0		Torquay	Yes	0	0	1	2
8	Bolton	Exeter	1	2	3	1
9	QPR	Bolton	1	1	1	2
Sheet9 (5)

Cell Formulas
Range	Formula
H1:M7	H1	=LET(n,-N2,A,TAKE(Table49[Home Team],n),B,TAKE(Table49[Away Team],n),tm,TRANSPOSE(SORT(UNIQUE(VSTACK(A,B)))),E,TAKE(Table49[Home Team 2nd Half],n),F,TAKE(Table49[Away Team 2nd Half],n),s,SEQUENCE(2*N2,1,1),W,INDEX(SORT(HSTACK(VSTACK(A,B),VSTACK(E,F)),1),s,1),X,INDEX(SORT(HSTACK(VSTACK(A,B),VSTACK(E,F)),1),s,2),Y,INDEX(SORT(HSTACK(VSTACK(A,B),VSTACK(F,E)),1),s,1),Z,INDEX(SORT(HSTACK(VSTACK(A,B),VSTACK(F,E)),1),s,2),sumOfCols,LAMBDA(column,SUM(column)),VSTACK({"Team","Less than 5 Matches?","Last 5 No of Games (2nd Half)","Last 5 No of Goals (2nd Half)","Last 5 No of Games Conceded (2nd Half)","Last 5 No of Goals Conceded (2nd Half)"},TRANSPOSE(VSTACK(tm,IF(N2>BYCOL(IF(SORT(VSTACK(A,B))=tm,1,0),sumOfCols),"Yes",""),BYCOL(IF(W=tm,IF(X>0,1,0),0),sumOfCols),BYCOL(IF(W=tm,IF(X>0,X,0),0),sumOfCols),BYCOL(IF(Y=tm,IF(Z>0,1,0),0),sumOfCols),BYCOL(IF(Y=tm,IF(Z>0,Z,0),0),sumOfCols)))))
Dynamic array formulas.

 

[jasonb75]

Occam's razor comes to mind

 

[cmowla]

Yeah. Just wanted to learn how to not carry formulas down, that's all! (And I made a mistake in the formula. It's referencing Table49 instead of Table4.)

 

[jasonb75]

Just wanted to learn how to not carry formulas down

We all like to learn new things, I think it's better to hold off on the fun / over the top suggestions until after the question has been resolved though, too much complication can cause confusion for the person asking the question. But that's just personal opinion

For what it's worth, I would suspect that the table will always be specific to one team, so whilst the ideas being discussed will be good to keep in mind for future use they could be considered moot in the current thread.