Calculate SUMIF based on changing variable

[David0227]

Hi,

In the 'Home Team' column & 'Away Team' area (Column D & E) the current rows' results SUM all the previous results for that team starting with the row directly above all the way up E.g. Row 15 Column D = SUMIF(B$5:B15,B16,D$5:D15) and Row 15 column E = =sUMIF(C$5:C15,C16,D$5:D15)

I'd like to be able to use the 'Number of Results to Count from bottom up' field (Row 1 column B) which is currently set to 5 (but will change to any number I want) to only count from bottom up the last 5 results (or whatever number I change it to ) results. So in the example below in row 15 column 4, the value would be 0 as it'd only sum Team 1'ss last 5 results from bottom up in Column C which just happens to stop in row 6 in this instance

	Column A	Column B	Column C	Column D	Column E
Row 1	Number of Results to Count from bottom up	5
Row 2
Row 3	Home Team	Away Team	Match Result (1 - Home Win, 0 - Draw, -1 Away Win)	Home Team	Away Team
Row 4	Team 1	Team 2	1
Row 5	Team 2	Team 3	0	0	0
Row 6	Team 3	Team 1	-1	0	0
Row 7	Team 4	Team 3	1	0	0
Row 8	Team 1	Team 2	0	1	1
Row 9	Team 2	Team 1	0	0	-1
Row 10	Team 4	Team 3	1	1	1
Row 11	Team 3	Team 2	1	-1	1
Row 12	Team 4	Team 1	1	2	-1
Row 13	Team 2	Team 3	-1	0	2
Row 14	Team 3	Team 4	-1	0	0
Row 15	Team 1	Team 4	0	1	-1

 

[jasonb75]

Welcome to Mr Excel!

I think that I have this right, the formula is progressive, so for Team 1, the last 5 in Row 15 will look back to Row 6, in Row 12 it will look back to Row 4. If there are less than 5 previous results for a team, then it will take the maximum available.

For posting examples, it is better if you use the xl2bb add-in (click the green button on the reply box toolbar). When you post samples as you did in post 1, everything is moved down and right of where it should be, meaning that 'Column B' in the table is 'Column C' in the fomulas, etc which can lead to a lot of confusion.

Book4.xlsx
	A	B	C	D	E
1	Number of Results to Count	5
2
3	Home Team	Away Team	Match Result (1 - Home Win, 0 - Draw, -1 Away Win)	Home Team	Away Team
4	Team 1	Team 2	1
5	Team 2	Team 3	0	1	0
6	Team 3	Team 1	-1	0	1
7	Team 4	Team 3	1	0	-1
8	Team 1	Team 2	0	0	1
9	Team 2	Team 1	0	1	0
10	Team 4	Team 3	1	1	0
11	Team 3	Team 2	1	1	1
12	Team 4	Team 1	1	2	0
13	Team 2	Team 3	1	2	2
14	Team 3	Team 4	-1	3	3
15	Team 1	Team 4	0	1	2
Sheet3

Cell Formulas
Range	Formula
D5:E15	D5	=IFERROR(SUMPRODUCT((ROW(A$4:A4)>=AGGREGATE(14,6,ROW(A$4:A4)/($A$4:$B4=A5),MIN($B$1,COUNTIF($A$4:$B4,A5))))*($A$4:$B4=A5)*$C$4:$C4),0)

 

[David0227]

Thank you so much - and points noted on etiquette

 

[jasonb75]

You're very welcome, thanks for the feedback

 

[David0227]

Hi,
I have another question on this one - is it possible to AVERAGE the last X outcomes rather than SUM them all up? I've tried tweaking your formula but I can't get it at all.
Thank you

 

[jasonb75]

For a conditional average you need to do sum / count, I think that I have this correct but I suggest doing some manual calculations to check that the results are as expected.

Book2
	A	B	C	D	E
1	Number of Results to Count	5
2
3	Home Team	Away Team	Match Result (1 - Home Win, 0 - Draw, -1 Away Win)	Home Team	Away Team
4	Team 1	Team 2	1
5	Team 2	Team 3	0	1	0
6	Team 3	Team 1	-1	0	1
7	Team 4	Team 3	1	0	-0.5
8	Team 1	Team 2	0	0	0.5
9	Team 2	Team 1	0	0.3333333	0
10	Team 4	Team 3	1	1	0
11	Team 3	Team 2	1	0.25	0.25
12	Team 4	Team 1	1	1	0
13	Team 2	Team 3	1	0.4	0.4
14	Team 3	Team 4	-1	0.6	1
15	Team 1	Team 4	0	0.2	0.5
Sheet7

Cell Formulas
Range	Formula
D5:E15	D5	=IFERROR(SUMPRODUCT((ROW(A$4:A4)>=AGGREGATE(14,6,ROW(A$4:A4)/($A$4:$B4=A5),MIN($B$1,COUNTIF($A$4:$B4,A5))))*($A$4:$B4=A5)*$C$4:$C4)/SUMPRODUCT((ROW(A$4:A4)>=AGGREGATE(14,6,ROW(A$4:A4)/($A$4:$B4=A5),MIN($B$1,COUNTIF($A$4:$B4,A5))))*($A$4:$B4=A5)),0)

 

[David0227]

Thank you so much

 

[David0227]

I have a new scenario that I'm really struggling to get right, it's similar to the first question and I'd really appreciate some help.

I'd like cell E13 to average all of Team 2's Ratings from their last 3 games (columns C & D) regardless of whether they are the home team or away team. I'd like the same principal to be applied in F13, using all of their last 3 games regardless of whether they are home or away team. I then want to copy that formula down columns E & F as new results come in.

Additional point is the number of games I want to look back can change depending depending on value in B1.

Thank you

Mr Excel Examples.xlsx
	A	B	C	D	E	F
1	Number of Games	3
2
3	Home Team	Away Team	Home Team Rating	Away Team Rating	Home Team Running Score	Away Team Running Score
4	Team 5	Team 6	1.2	1.6
5	Team 4	Team 5	2.2	1
6	Team 6	Team 5	3.2	1.6
7	Team 3	Team 4	3.3	1.9
8	Team 5	Team 4	5.2	3.2
9	Team 2	Team 3	6.2	4.2
10	Team 4	Team 3	2.4	5.2
11	Team 1	Team 2	1.8	6.2
12	Team 3	Team 2	2.2	1.6
13	Team 2	Team 4			4.67	6.23
Sheet1

Cell Formulas
Range	Formula
E13	E13	=(D12+D11+C9)/3
F13	F13	=C10+D8+D7/3

 

[jasonb75]

I think that this is what you need, the second result in your example appears incorrect due to missing parenthesis.

Book123.xlsm
	A	B	C	D	E	F
1	Number of Games	3
2
3	Home Team	Away Team	Home Team Rating	Away Team Rating	Home Team Running Score	Away Team Running Score
4	Team 5	Team 6	1.2	1.6
5	Team 4	Team 5	2.2	1	0	1.2
6	Team 6	Team 5	3.2	1.6	1.6	1.1
7	Team 3	Team 4	3.3	1.9	0	2.2
8	Team 5	Team 4	5.2	3.2	1.266666667	2.05
9	Team 2	Team 3	6.2	4.2	0	3.3
10	Team 4	Team 3	2.4	5.2	2.433333333	3.75
11	Team 1	Team 2	1.8	6.2	0	6.2
12	Team 3	Team 2	2.2	1.6	4.233333333	6.2
13	Team 2	Team 4			4.666666667	2.5
Sheet22

Cell Formulas
Range	Formula
E5:F13	E5	=IFERROR(SUMPRODUCT((ROW(A$4:A4)>=AGGREGATE(14,6,ROW(A$4:A4)/($A$4:$B4=A5),MIN($B$1,COUNTIF($A$4:$B4,A5))))*($A$4:$B4=A5)*$C$4:$D4)/SUMPRODUCT((ROW(A$4:A4)>=AGGREGATE(14,6,ROW(A$4:A4)/($A$4:$B4=A5),MIN($B$1,COUNTIF($A$4:$B4,A5))))*($A$4:$B4=A5)),0)

 

[David0227]

Thank you, works perfectly!