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:
Sheet 2 (sorted by Home Team and in date order) contains this:
Sheet 3 (sorted by away team and in date order)
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 | |||||||
