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

jfriedman · Jan 18, 2024

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!

0.00

#REF!

3

Arsenal

4

Arsenal

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

4

9/3/23

Arsenal

Man United

3

1

5

9/24/23

Arsenal

Tottenham

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

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

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 · Jan 18, 2024

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 · Jan 18, 2024

felixstraube said:
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 · Jan 18, 2024

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

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/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

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

21/10/2023

Chelsea

Arsenal

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

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 · Jan 18, 2024

@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

felixstraube · Jan 18, 2024

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

jfriedman · Jan 18, 2024

felixstraube said:
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

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

=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))

3

Arsenal

4

Arsenal

Sheet1

Cell Formulas
Range	Formula
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

Here is sheet2:

SoccerRatings_v1.xlsx

A

B

C

D

E

1

Date

Home

Away

HomeGoals

AwayGoals

2

8/12/23

Arsenal

Nott'm Forest

2

1

3

8/26/23

Arsenal

Fulham

2

4

9/3/23

Arsenal

Man United

3

1

5

9/24/23

Arsenal

Tottenham

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

Sheet2

Sheet3:

SoccerRatings_v1.xlsx

A

B

C

D

E

1

Date

Home

Away

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

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

11

12/31/23

Fulham

Arsenal

2

1

Sheet3

felixstraube · Jan 19, 2024

Here is the working file to download:

SoccerTeam.zip

jfriedman · Jan 19, 2024

felixstraube said:
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

felixstraube · Jan 19, 2024

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.

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

jfriedman

New Member

Excel Facts

felixstraube

Active Member

jfriedman

New Member

felixstraube

Active Member

jfriedman

New Member

felixstraube

Active Member

jfriedman

New Member

felixstraube

Active Member

jfriedman

New Member

felixstraube

Active Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock