Difference of last N rows Based on multiple conditions using two columns

[popov]

Hello,

I have the following problem. I would like to calculate the goal difference for the last N games for each team specified (assuming the Date is already sorted, ie. 6 being the latest game played).

For example if I want to calculate the goal differences for England in their last 3 games I would like to have (1-1) + (3-0) + (2-1) = 4

I hope this is clear enough.

Thank you in advance.

Kind regards,
Pierre

 

[BSALV]

testing.xlsx
	A	B	C	D	E	F	G	H	I
1								last N	delta
2		england	germany	6	0		England	3	4
3		italy	spain	5	1		germany	1	1
4		usa	england	1	2		greece	1	-3
5		england	greece	3	0		romania	2	-1
6		germany	romania	3	2		usa	1	-1
7		romania	england	1	1			8	0
Blad6

Cell Formulas
Range	Formula
H2:H6	H2	=COUNTIF($B$4:$C$7,G2)
I2:I6	I2	=SUMPRODUCT(($D$4:$D$7-$E$4:$E$7)*(($B$4:$C$7=G2)-($C$4:$C$7=G2)))
H7:I7	H7	=SUM(H2:H6)

 

[popov]

testing.xlsx
	A	B	C	D	E	F	G	H	I
1								last N	delta
2		england	germany	6	0		England	3	4
3		italy	spain	5	1		germany	1	1
4		usa	england	1	2		greece	1	-3
5		england	greece	3	0		romania	2	-1
6		germany	romania	3	2		usa	1	-1
7		romania	england	1	1			8	0
Blad6

Cell Formulas
Range	Formula
H2:H6	H2	=COUNTIF($B$4:$C$7,G2)
I2:I6	I2	=SUMPRODUCT(($D$4:$D$7-$E$4:$E$7)*(($B$4:$C$7=G2)-($C$4:$C$7=G2)))
H7:I7	H7	=SUM(H2:H6)

Hi Bsalv,

Thank you for the quick response!

Would it be possible to select all the cells and automatically calculate what you calculated on the basis of each team's last 3 games. That it instead of selecting the cells on which to perform your formula, to be able to select the whole range and use the value in Last N as a parameter that I can later change and the formula for the delta to still be valid? I hope this is clear enough!

Kind regards,
Pierre

 

[BSALV]

i'm not strong in matrixformulas, so yes, it's possible, but i can't give an answer.

 

[Fluff]

I feel sure there must be a simpler way, but how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	england	germany	6	0		England	3	4
3	2	italy	spain	5	1
4	3	usa	england	1	2
5	4	england	greece	3	0
6	5	germany	romania	3	2
7	6	romania	england	1	1
8
Lists

Cell Formulas
Range	Formula
I2	I2	=LET(f,SORT(FILTER(A2:E7,(B2:B7=G2)+(C2:C7=G2)),1,-1),a,INDEX(f,SEQUENCE(H2),{2,3,4,5}),SUM((INDEX(a,,3)-INDEX(a,,4))*IF(INDEX(a,,1)=G2,1,-1)))

 

[eastrand]

Not any better, but here another way.

Book3
	A	B	C	D	E	F	G	H	I
1
2	1	england	germany	6	0		England	3	4
3	2	italy	spain	5	1
4	3	usa	england	1	2
5	4	england	greece	3	0
6	5	germany	romania	3	2
7	6	romania	england	1	1
Sheet1

Cell Formulas
Range	Formula
I2	I2	=SUM(INDIRECT("D" & (MIN(ROW(A2:A7))+ROWS(A2:A7))-H2 &":D7"),-INDIRECT("E" & (MIN(ROW(A2:A7))+ROWS(A2:A7))-H2 &":E7"))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Difference of last N rows Based on multiple conditions using two columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.