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

jfriedman

New Member
Joined
Jan 18, 2024
Messages
11
Office Version
  1. 2021
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOPQ
1DATETOTAL GOALSHOME SIDEAWAY SIDEHOME ATTACKHOME DEFENCEHOME RATINGAWAY ATTACKAWAY DEFENCEAWAY RATINGRATING DIFFHOME SCOREAWAY SCOREGOALSODDSESTIMATED ODDSVALUE
2Arsenal#REF!#REF!#REF!0.00#REF!#REF!#REF!#REF!#REF!#REF!
3Arsenal#REF!#REF!0.00#REF!0.00#REF!#REF!#REF!#REF!
4Arsenal#REF!#REF!0.00#REF!0.00#REF!#REF!#REF!#REF!
Sheet1
Cell Formulas
RangeFormula
E2E2=AVERAGEIFS(OFFSET(Sheet2!$D$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
F2F2=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:J4G2=(E2-F2)/6
F3F3=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
F4F4=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
K2:K4K2=G2-J2
L2:L4L2=((E2+I2)/6)/2
M2:M4M2=((H2+F2)/6)/2
N2:N4N2=L2+M2
P2:P4P2=(4-K2)/2+1
Q2:Q4Q2=O2/P2*100



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

SoccerRatings_v1.xlsx
ABCDE
1DateHomeTeamAwayTeamHomeGoalsAwayGoals
28/12/23ArsenalNott'm Forest21
38/26/23ArsenalFulham22
49/3/23ArsenalMan United31
59/24/23ArsenalTottenham22
610/8/23ArsenalMan City10
710/28/23ArsenalSheffield United50
811/11/23ArsenalBurnley31
912/2/23ArsenalWolves21
1012/17/23ArsenalBrighton20
1112/28/23ArsenalWest Ham02
128/20/23Aston VillaEverton40
139/16/23Aston VillaCrystal Palace31
149/30/23Aston VillaBrighton61
Sheet2



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

SoccerRatings_v1.xlsx
ABCDE
1DateHomeTeamAwayTeamHomeGoalsAwayGoals
28/21/23Crystal PalaceArsenal01
39/17/23EvertonArsenal01
49/30/23BournemouthArsenal04
510/21/23ChelseaArsenal22
611/4/23NewcastleArsenal10
711/25/23BrentfordArsenal01
812/5/23LutonArsenal34
912/9/23Aston VillaArsenal10
1012/23/23LiverpoolArsenal11
1112/31/23FulhamArsenal21
128/12/23NewcastleAston Villa51
138/27/23BurnleyAston Villa13
Sheet3
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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?
 
Upvote 0
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
 
Upvote 0
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
Code:
(HomeGoals - HomeDefense)/5
or
Code:
HomeGoals - HomeDefense/5
?

SoccerTeam.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1HomeAway
2DateTotal GoalsHome SideAway SideHome AttackHome DefenceHome RatingAway AttackAway DefenceAway RatingRating DiffHome ScoreAway ScoreExpected GoalsOddsPredictedValueDateHomeTeamAwayTeamHomeGoalsAwayGoalsDateHomeTeamAwayTeamHomeGoalsAwayGoals
3Arsenal1241.677012/08/2023ArsenalNott'm Forest2112/08/2023NewcastleAston Villa51
420/08/2023Aston VillaEverton4021/08/2023Crystal PalaceArsenal01
526/08/2023ArsenalFulham2227/08/2023BurnleyAston Villa13
603/09/2023ArsenalMan United3117/09/2023EvertonArsenal01
716/09/2023Aston VillaCrystal Palace3130/09/2023BournemouthArsenal04
824/09/2023ArsenalTottenham2221/10/2023ChelseaArsenal22
930/09/2023Aston VillaBrighton6104/11/2023NewcastleArsenal10
1008/10/2023ArsenalMan City1025/11/2023BrentfordArsenal01
1128/10/2023ArsenalSheffield United5005/12/2023LutonArsenal34
1211/11/2023ArsenalBurnley3109/12/2023Aston VillaArsenal10
1302/12/2023ArsenalWolves2123/12/2023LiverpoolArsenal11
1417/12/2023ArsenalBrighton2031/12/2023FulhamArsenal21
1528/12/2023ArsenalWest Ham02
Sheet1
Cell Formulas
RangeFormula
E3E3=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) )
F3F3=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,J3G3=(E3-F3)/5
H3H3=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) )
I3I3=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) )
 
Upvote 0
@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".

Code:
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
 
Upvote 0
Did your conver your Home and Away data in tables? And named them "Home" and "Away"?

Yes, I changed the names in Sheet2 and Sheet3.

First few rows of Sheet1:
SoccerRatings_v1.xlsx
ABCDEFGHIJKLMNOPQ
1DATETOTAL GOALSHOME SIDEAWAY SIDEHOME ATTACKHOME DEFENCEHOME RATINGAWAY ATTACKAWAY DEFENCEAWAY RATINGRATING DIFFHOME SCOREAWAY SCOREGOALSODDSESTIMATED ODDSVALUE
2Arsenal=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))#REF!#VALUE!0.00#VALUE!#VALUE!#REF!#VALUE!#VALUE!#VALUE!
3Arsenal#REF!#REF!0.00#REF!0.00#REF!#REF!#REF!#REF!
4Arsenal#REF!#REF!0.00#REF!0.00#REF!#REF!#REF!#REF!
Sheet1
Cell Formulas
RangeFormula
F2F2=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:J4G2=(E2-F2)/6
F3F3=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
F4F4=AVERAGEIFS(OFFSET(Sheet2!$E$2, MATCH(C2, Sheet2!$B$2:$B$100, 0)-5, 0, 5, 1), Sheet2!$B$2:$B$100, C2)
K2:K4K2=G2-J2
L2:L4L2=((E2+I2)/6)/2
M2:M4M2=((H2+F2)/6)/2
N2:N4N2=L2+M2
P2:P4P2=(4-K2)/2+1
Q2:Q4Q2=O2/P2*100



Here is sheet2:
SoccerRatings_v1.xlsx
ABCDE
1DateHomeAwayHomeGoalsAwayGoals
28/12/23ArsenalNott'm Forest21
38/26/23ArsenalFulham22
49/3/23ArsenalMan United31
59/24/23ArsenalTottenham22
610/8/23ArsenalMan City10
710/28/23ArsenalSheffield United50
811/11/23ArsenalBurnley31
912/2/23ArsenalWolves21
1012/17/23ArsenalBrighton20
1112/28/23ArsenalWest Ham02
Sheet2



Sheet3:
SoccerRatings_v1.xlsx
ABCDE
1DateHomeAwayHomeGoalsAwayGoals
28/21/23Crystal PalaceArsenal01
39/17/23EvertonArsenal01
49/30/23BournemouthArsenal04
510/21/23ChelseaArsenal22
611/4/23NewcastleArsenal10
711/25/23BrentfordArsenal01
812/5/23LutonArsenal34
912/9/23Aston VillaArsenal10
1012/23/23LiverpoolArsenal11
1112/31/23FulhamArsenal21
Sheet3
 
Upvote 0
Here is the working file to download:

SoccerTeam.zip

@felixstraube thank you so much, this is perfect! One question - how did you get this to work? The formula looks incredibly simple.

I see you have some data in Sheet2, do I need to do anything, or can I just paste my football results into the orange and yellow boxes in Sheet1?

Thanks again!
Jeff
 
Upvote 0
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:

Excel Formula:
=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:
Excel Formula:
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.
Excel Formula:
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.
Excel Formula:
goals, FILTER(Home[HomeGoals], (Home[HomeTeam]=team)*(Home[Date]>=minDate)),
and last you sum them up.
 
Upvote 1

Forum statistics

Threads
1,224,351
Messages
6,178,060
Members
452,822
Latest member
MtC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top