Calculate last 5 soccer team goals using data from another sheet on same workbook.

[jfriedman]

Hopefully someone can help me with this Excel problem that I'm trying to solve. I'll try to be as clear as possible. I'm using Excel for Mac, version 16.81.

I have a workbook called SoccerRatings. In this workbook I have Sheet1 and row 1 contains the headings. The columns are named as such:

A - Date

B - Total Goals

C - Home Side

D - Away Side

E - Home Attack

F - Home Defence

G - Home Rating

H - Away Attack

I - Away Defence

J - Away Rating

K - Rating Diff

L - Home Score

M - Away Score

N - Expected Goals

O - Odds

P - Predicted

Q - Value

My area of interest lies in calculating the columns E F H I on Sheet1 using data from Sheet2 and Sheet3.

Sheet2 relates to results from the home team. Sheet3 relates to results for the away team. I seperate these to avoid confusion. I download these soccer results from a historic provider.

In Sheet2, row 1 contains the column headings. The columns are named:

A - Date

B - Home

C - Away

D - Home Goals

E - Away Goals



In Sheet2, I have everything sorted alphabetically by column B, which is the name of the Home team, and I have posted a picture to illustrate this. Please note that everything falls into date order that these teams played, which is a great help.

For example, say Arsenal are playing as the Home Team, how can I calculate the goals from their last 5 games, as listed on Sheet2, and have that total appear in Sheet1 under Home Attack (column E)?

Sheet3 is sorted by Away Team in date order, so I'm guessing whatever I do for the Home Team, it'll be interchangeable for the Away Team too.

The data in Sheet2 and Sheet3 will be constantly updated, but I always need the total sum to be drawn from the last 5 games.

I need to be able to do this for the Home Team and Away Team. I download fixtures every week, so I hope I can just cut and paste what teams are playing into the Home Side and Away Side columns, and numbers will do the rest for me.

My question is - how can I do this? I've been stumped on this for a few weeks and am no further on.

The formulas need to be dynamic to account for the different team names.


My Excel understanding is very basic, and I tried to piece together some formulas, which don't work I'm afraid:

This is for the Home Attack box.

=SUMIFS(Sheet2!$D:$D,Sheet2!$B:$B,Sheet1!C2,Sheet2!$A:$A,">"&MAX(IF(Sheet2!$B:$B=Sheet1!C2,Sheet2!$A

Home attack -

=AVERAGEIFS(OFFSET(Sheet2!$D$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)

Home defence:

=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)

Away Attack:

=AVERAGEIFS(OFFSET(Sheet2!$D$2, MATCH(D2, Sheet2!$C$2:$C$100, 0)-5, 0, 5, 1), Sheet2!$C$2:$C$100, D2)

Away Defence:

=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(D2, Sheet2!$C$2:$C$100, 0)-5, 0, 5, 1), Sheet2!$C$2:$C$100, D2)

Sheet1 Contains this:

SoccerRatings_v1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	DATE	TOTAL GOALS	HOME SIDE	AWAY SIDE	HOME ATTACK	HOME DEFENCE	HOME RATING	AWAY ATTACK	AWAY DEFENCE	AWAY RATING	RATING DIFF	HOME SCORE	AWAY SCORE	GOALS	ODDS	ESTIMATED ODDS	VALUE
2			Arsenal		#REF!	#REF!	#REF!			0.00	#REF!	#REF!	#REF!	#REF!		#REF!	#REF!
3			Arsenal			#REF!	#REF!			0.00	#REF!	0.00	#REF!	#REF!		#REF!	#REF!
4			Arsenal			#REF!	#REF!			0.00	#REF!	0.00	#REF!	#REF!		#REF!	#REF!
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGEIFS(OFFSET(Sheet2!$D$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
F2	F2	=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
G2:G4,J2:J4	G2	=(E2-F2)/6
F3	F3	=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
F4	F4	=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
K2:K4	K2	=G2-J2
L2:L4	L2	=((E2+I2)/6)/2
M2:M4	M2	=((H2+F2)/6)/2
N2:N4	N2	=L2+M2
P2:P4	P2	=(4-K2)/2+1
Q2:Q4	Q2	=O2/P2*100

Sheet 2 (sorted by Home Team and in date order) contains this:

SoccerRatings_v1.xlsx
	A	B	C	D	E
1	Date	HomeTeam	AwayTeam	HomeGoals	AwayGoals
2	8/12/23	Arsenal	Nott'm Forest	2	1
3	8/26/23	Arsenal	Fulham	2	2
4	9/3/23	Arsenal	Man United	3	1
5	9/24/23	Arsenal	Tottenham	2	2
6	10/8/23	Arsenal	Man City	1	0
7	10/28/23	Arsenal	Sheffield United	5	0
8	11/11/23	Arsenal	Burnley	3	1
9	12/2/23	Arsenal	Wolves	2	1
10	12/17/23	Arsenal	Brighton	2	0
11	12/28/23	Arsenal	West Ham	0	2
12	8/20/23	Aston Villa	Everton	4	0
13	9/16/23	Aston Villa	Crystal Palace	3	1
14	9/30/23	Aston Villa	Brighton	6	1
Sheet2

Sheet 3 (sorted by away team and in date order)

SoccerRatings_v1.xlsx
	A	B	C	D	E
1	Date	HomeTeam	AwayTeam	HomeGoals	AwayGoals
2	8/21/23	Crystal Palace	Arsenal	0	1
3	9/17/23	Everton	Arsenal	0	1
4	9/30/23	Bournemouth	Arsenal	0	4
5	10/21/23	Chelsea	Arsenal	2	2
6	11/4/23	Newcastle	Arsenal	1	0
7	11/25/23	Brentford	Arsenal	0	1
8	12/5/23	Luton	Arsenal	3	4
9	12/9/23	Aston Villa	Arsenal	1	0
10	12/23/23	Liverpool	Arsenal	1	1
11	12/31/23	Fulham	Arsenal	2	1
12	8/12/23	Newcastle	Aston Villa	5	1
13	8/27/23	Burnley	Aston Villa	1	3
Sheet3

 

[felixstraube]

Here is the working file to download:

SoccerTeam.zip

 

[felixstraube]

Happy to help. Thanks for the feedback.
Yes you can ignore the data in sheet 2, and paste your results in the two tables.
You can cut and paste the tables in another sheet if you like. It will work the same.

I'll explain how this formula work for example:

=LET(
team, $C3,
gamesDates, FILTER(Home[Date], Home[HomeTeam]=team),
minDate, IFERROR(LARGE(gamesDates,5), MAX(gamesDates)),
goals, FILTER(Home[HomeGoals], (Home[HomeTeam]=team)*(Home[Date]>=minDate)),
SUM(goals)
)

the function LET lets you define variables (or "names" as Microsoft calles them), in pairs of arguments. You assign the value in cell $C3 to the variable "team" in the first pair of arguments, than you get the result of the formula FILTER(Home[Date], Home[HomeTeam]=team) into the variable "gamesDates" and so forth. The last argument of the LET function lets you do some calculation that is then the return value/array.

Basically what the formula does is: First you filter the dates by team (in our example Arsenal) with this line:

gamesDates, FILTER(Home[Date], Home[HomeTeam]=team),

then you get the 5th larges date or the max date if there are less than 5 date for the given team.

minDate, IFERROR(LARGE(gamesDates,5), MAX(gamesDates)),

then you filter the HomeGoals column by team and only dates that are iqual o greater than this minDate. So ideally if there are 5 games or more you get the goals for these last 5.

goals, FILTER(Home[HomeGoals], (Home[HomeTeam]=team)*(Home[Date]>=minDate)),

and last you sum them up.

 

[felixstraube]

You are welcome.
I did that because im lazy and it is easier for me to have the table in the same sheet when you are working with formulas (no need to change sheets, just click and drag the need range).
You can move them with cut and paste anywhere you like (any sheet).

 

[RoryA]

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:
Calculate last 5 soccer team goals using data from another sheet on same workbook.

There is no need to repeat the link(s) provided above but if you have posted the question at other 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.

 

[felixstraube]

Can you explain to me how this are calculated in plain english?
Home Attack
Home Defense
Home Rating

Attack I guess its the total count of goals, right?
But defense and rating i don't know.

Those three calculations are the same for Away? Just with data from the Away table?

 

[jfriedman]

Can you explain to me how this are calculated in plain english?
Home Attack
Home Defense
Home Rating

Attack I guess its the total count of goals, right?
But defense and rating i don't know.

Those three calculations are the same for Away? Just with data from the Away table?

Hi Felix,

Home Attack = total number of goals scored by a particular team in their last 5 games
Home Defense = total number of goals conceded by the home team in their last 5 games.
Home rating = home attack figure - home defense figure ÷ 5.

yes, those calculations will be the same for the away team too.

Thank you,
Jeff

 

[felixstraube]

How about this?:
I converted the data in sheet2 and sheet3 into table "Home" and "Away". This way the formulas are more readable and it will be easier to update/add new result/games to your data.
I used the table on the same sheet as the calculation but they can be anyway in the book.
Let me know if this is giving the results you need. Not sure about the rating. Is it

(HomeGoals - HomeDefense)/5

or

HomeGoals - HomeDefense/5

?

SoccerTeam.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1																					Home						Away
2	Date	Total Goals	Home Side	Away Side	Home Attack	Home Defence	Home Rating	Away Attack	Away Defence	Away Rating	Rating Diff	Home Score	Away Score	Expected Goals	Odds	Predicted	Value				Date	HomeTeam	AwayTeam	HomeGoals	AwayGoals		Date	HomeTeam	AwayTeam	HomeGoals	AwayGoals
3			Arsenal		12	4	1.6	7	7	0											12/08/2023	Arsenal	Nott'm Forest	2	1		12/08/2023	Newcastle	Aston Villa	5	1
4																					20/08/2023	Aston Villa	Everton	4	0		21/08/2023	Crystal Palace	Arsenal	0	1
5																					26/08/2023	Arsenal	Fulham	2	2		27/08/2023	Burnley	Aston Villa	1	3
6																					03/09/2023	Arsenal	Man United	3	1		17/09/2023	Everton	Arsenal	0	1
7																					16/09/2023	Aston Villa	Crystal Palace	3	1		30/09/2023	Bournemouth	Arsenal	0	4
8																					24/09/2023	Arsenal	Tottenham	2	2		21/10/2023	Chelsea	Arsenal	2	2
9																					30/09/2023	Aston Villa	Brighton	6	1		04/11/2023	Newcastle	Arsenal	1	0
10																					08/10/2023	Arsenal	Man City	1	0		25/11/2023	Brentford	Arsenal	0	1
11																					28/10/2023	Arsenal	Sheffield United	5	0		05/12/2023	Luton	Arsenal	3	4
12																					11/11/2023	Arsenal	Burnley	3	1		09/12/2023	Aston Villa	Arsenal	1	0
13																					02/12/2023	Arsenal	Wolves	2	1		23/12/2023	Liverpool	Arsenal	1	1
14																					17/12/2023	Arsenal	Brighton	2	0		31/12/2023	Fulham	Arsenal	2	1
15																					28/12/2023	Arsenal	West Ham	0	2
Sheet1

Cell Formulas
Range	Formula
E3	E3	=LET(team, $C3, gamesDates, FILTER(Home[Date], Home[HomeTeam]=team), minDate, IFERROR(LARGE(gamesDates,5), MAX(gamesDates)), goals, FILTER(Home[HomeGoals], (Home[HomeTeam]=team)*(Home[Date]>=minDate)), SUM(goals) )
F3	F3	=LET(team, $C3, homeGamesDates, FILTER(Home[Date], Home[HomeTeam]=team), minDate, IFERROR(LARGE(homeGamesDates,5), MAX(homeGamesDates)), goals, FILTER(Home[AwayGoals], (Home[HomeTeam]=team)*(Home[Date]>=minDate)), SUM(goals))
G3,J3	G3	=(E3-F3)/5
H3	H3	=LET(team, $C3, homeGamesDates, FILTER(Away[Date], Away[AwayTeam]=team), minDate, IFERROR(LARGE(homeGamesDates,5), MAX(homeGamesDates)), goals, FILTER(Away[AwayGoals], (Away[AwayTeam]=team)*(Away[Date]>=minDate)), SUM(goals) )
I3	I3	=LET(team, $C3, homeGamesDates, FILTER(Away[Date], Away[AwayTeam]=team), minDate, IFERROR(LARGE(homeGamesDates,5), MAX(homeGamesDates)), goals, FILTER(Away[HomeGoals], (Away[AwayTeam]=team)*(Away[Date]>=minDate)), SUM(goals) )

 

[jfriedman]

@felixstraube , thanks for your reply. I must be doing something wrong because I have tried to copy and paste and also typed in the formula and I'm getting the below error message. My cell format is on "general".

There's a problem with this formula.

Not trying to type a formula?
When the first character is an equal ("=") or minus ("-") sign, Excel thinks it's a formula:

• you type:   =1+1, cell shows:   2

To get around this, type an apostrophe ( ' ) first:

• you type:   '=1+1, cell shows:   =1+1

 

[felixstraube]

Did your conver your Home and Away data in tables? And named them "Home" and "Away"?