Football/Soccer data, teams giving up a lead or recovering a goal deficit

[AlexCarl]

Hi all,

I was wondering if some or one of you might be able to help with something I'm stuck.

I currently have spreadsheets, like the one attached, that has matches with the goal minutes of each goal, for home and away team. What I would like to end up with is a spreadsheets that tells me how often teams gave up a lead or for example recovered a goal deficit. It would be greatly appreciated if you guys could help as I'm really stuck with this at the moment.

In the spreadsheet are 2 sheets, one with the data and one with the ideal output based on formulas,etc. but obviously any better suggestions would be welcome.

EPL_14-15_1.xlsx

Looking forward to hearing from you.

Many thanks,
Alex

[AlanY]

I'd a go of your interesting problem.

see attached.

first of all I'd increased no of goals per team to 10, just in case.

Column AR is to check if one of the team didn't scored at all, if so, nothing done.
Column AS is just Home goals minus Away goals.
Column AT is to check if the home team scored first.

Column AU for the Home team is based on 1 of your 4 criteria

1 When Goal Up Gives Lead Back Yes
2 When Goal Up Gives Lead Back No
3 When Goal Down Gets Deficit Back Yes
4 When Goal Down Gets Deficit Back No

Column AV, similar for the away team.

Once you have your data sheet populated I suppose you can do a countif for the team to meet one of the criteria.

hope that helps.

[AlexCarl]

Hi Alan,

Thanks so much for having a look at this. I've had a look through your suggestion and I noticed that in it's current format a team could go up 3:0 and concede one goal at some point but the formula would tell you that the team has given a lead back even though it didn't.
I've added a running score option which I thought might help with getting the formulas we need. Below the first few games I have added the possible Current Scores for each "Current Score" "CS". You think that could work? Once I have added minutes of the games the cells with the Current Score could automatically populate with the right formulas?

I have attached the spreadsheet from you with the additional columns. Would be great to get your view on this.

Many thanks,
Alex

EPL_14-15_1.xlsx

[AlanY]

yes, you're correct.
the formula in Column AT were transposed by mistake, should be =MIN(AH2:AL2)-MIN(X2:AB2), see attached.

as it stands, Column AT check which team scored first and Column AS with nett goals that should be enough to fulfill your goals (no pun intended).

[AlexCarl]

Hi Alan,

Thanks for your swift reply, that's great.

Just tried another scenario with a team leading 2:0 and loses the lead to either go 2:2 or lose 2:3, but the formula doesn't change. What do you think?

Alex

[AlanY]

Alex, see attached in green.

the formula yielded 1 for both, i.e. When Goal Up then Gives Lead Back.
is that what you're expected.

have you changed Columns E & F? as the formula use them

[AlexCarl]

I have only played around with a view possible results and added goal minutes based on these scenarios. For example if I know change the 2nd green line, the goal minutes to 15, 22 and leave 10,20 for the second game the score would be that Team 2 goes 1:0 up, then Team 1 equalises (gets deficit back yes) and team 2 loses goal advantage but the formula says Team 1 (4 When Goal Down Gets Deficit Back No) and Team 2 (2 When Goal Up Gives Lead Back No.) Even though Team 2 lost the lead twice and Team 1 recovered twice. Does that help?

Again thanks for your help, really appreciated.

[AlanY]

i think i might misunderstood your original condition statements (1-4).

for
(4 When Goal Down Gets Deficit Back No)

I interpreted it as, (When Goal Down , Gets Deficit Back - No), ie with goal down (opponent scored first) but failed to get deficit back.

for your example

CS 0-1, 0-2, 1-1, 2-2, 2-3 which of the 1-4 are you expected?

[AlexCarl]

This is exactly what I need to figure out. Cause in essence you can have multiple situations coming up within one game. You can lose the lead, gain it back, lose it again. Quite often it happens that teams are 2:0 up and then draw or lose, often when the weaker team gets a freakish lead in the 1st half.
At the same time, you would like to know, even if a team wins in the end 3:1, once it was 1:0 did they equalise or steam to 3:0 and therefore where always leading.

From your point of view do you think we could record multiple situation? Or do we have to stick with 1-4 happening only once a game?

[AlanY]

it's really what are you trying to get out of all these.
obviously, your suggestion of current score (CS) will tell the whole story but needed a bit more work to it.

[AlexCarl]

Hi Alan,

Had a think about it last night and checked a lot of current scores. The story needs to be that we can tell if a team has lost a one goal lead or two goal lead and the other way around if a team made up a one goal or two goal deficit. Does this mean we need 1-16, instead of 1-4, as we also want to capture the end result when a team loses the lead are they in the end winning, drawing or losing? If too complicated I would say the minimum we want to get out of it, is if, even if a team won 2:1 has their been a 1:1 game state, so we can say if a team scored the first goal, the likelyhood of the 2nd goal coming from the other team is x%.


1 When 1 Goal Up Gives Lead Back Yes, full time result win
2 When 1 Goal Up Gives Lead Back Yes, full time result draw
3 When 1 Goal Up Gives Lead Back Yes, full time result loss
4 When 1 Goal Up Gives Lead Back No, which means win

5 When 1 Goal Down Gets Deficit Back Yes, full time result win
6 When 1 Goal Down Gets Deficit Back Yes, full time result draw
7 When 1 Goal Down Gets Deficit Back Yes, full time result loss
8 When 1 Goal Down Gets Deficit Back No, which means loss

9 When 2 Goal Up Gives Lead Back Yes, full time result win
10 When 2 Goal Up Gives Lead Back Yes, full time result draw
11 When 2 Goal Up Gives Lead Back Yes, full time result loss
12 When 2 Goal Up Gives Lead Back No, which means win

13 When 2 Goal Down Gets Deficit Back Yes, full time result win
14 When 2 Goal Down Gets Deficit Back Yes, full time result draw
15 When 2 Goal Down Gets Deficit Back Yes, full time result loss
16 When 2 Goal Down Gets Deficit Back No, which means loss

Does that make sense?
Or do you think there is a better way than that, or will it be too much and we only can do 1 goal leads?

Thanks,
Alex

[AlanY]

Hi Alex,

I'm working on something that might or might not be what you're after, see attached.

Row 2 are the times (in minutes) of the goals by both teams.
Row 3 works out the sequence of the goals being scored and show in Rows 5&6.

Row 9 works out the nett goals (Home-Away).

Cell V9 works out the max home lead and V10 for home deficit.

Also, Cell V4 works out max consecutive home goals, likewise V5 for the away team.

So, once Row 2 populated for that particular match it should be able to see if the team throw away a commanding 'X' goals lead with 'Y' no of consecutive goals etc.

What do you think?

Alan

[AlanY]

I've tidied up the spreadsheet with some dummy data (Columns E to X) for testing and made it slightly more 'users-friendly', see attached. Hidden columns Z to DE are the working part of the spreadsheet.

Column
DF is the final score
DH shows home win, away win or draw.
DI is the max home lead
DJ is the max home deficit

for example, if you select home team (Cell C1) - Man United.

Untitled.jpg

you can see that against QPR they thrown away a 1 goal lead and lost 6-8 eventually.

another example, you can find out all the matches that teams that had thrown away advantages (final result lost/drawn) by de-select the home win on Column DH and '0' on Column DI (i.e. with goal advantages).

Untitled1.jpg

[AlexCarl]

Alan this looks genius work. amazing. I haven't had time to go through it in detail yet.

Based on all this populated data would there now be a way to basically select all Man Utd games and then see a "dashboard" that tells you, Man Utd have won 33% of their games when 1:0 up and lost 50% when 0:1 down. What you have created here, might already answer it, just need to have a proper look at it today, which I will do in a couple of hours. But I got to say this looks fantastic and I appreciate your help on this.

Alex

[AlanY]

glad that you like it.
i'm having fun and learning from doing it

[AlanY]

just added another column/filter DH for the first goal by home/away team, see attached

[AlexCarl]

Hi Alan,

Hope you had a great weekend.

Had a bit of time to go through the sheet and it's excellent.

One scenario I still would be after would be the following. I have attached a spreadsheet with it and the 2 lines in yellow are the two game states I try to differentiate. If I look at the data I see that if a FT result was 2:1 and one team reached once a two goal lead, it never reached draw game state (1:1), how would it be possible to get this data now for many games into a dashboard format. So that it's not necessary to browse manually through x amount of games?
So that it takes all games were team 1 scored the first goal and then tells me how often they scored the 2nd goal or conceded the first goal.

Is it easier if I add a second sheet on how this dashboard would look or do you have an idea in mind?

Thanks,
Alex

[AlanY]

hi Alex,

Weekend was great but just a couple of days too short, thanks.
You forgot to attach the spreadsheet.

Alan

[AlexCarl]

Just noticed it. Here it is.

Book4_Alex.xlsx

[AlanY]

Alex,

I've added an extra column at the end to show number of times of the scores were level thus can be filtered out accordingly.

One thing I haven't done is to distinguish goals scored one after the other, i.e. both recorded the same time in minutes. I suppose you have to manually add 0.1s to the latter goal to make it work.

Alan

[AlexCarl]

Brilliant Alan, I will study this over dinner. Thanks again for the swift response.

Alex

[AlexCarl]

Hi Alan,

I'm currently adding the real data for this years Premier League season with all the goals in goal minutes. Hope to have this done by Saturday. I will show you what I have done once it's ready.

Really appreciate your help with this. Which part of London are you from by they way?

Alex

[AlanY]

Hi Alan,

I'm currently adding the real data for this years Premier League season with all the goals in goal minutes. Hope to have this done by Saturday. I will show you what I have done once it's ready.

Really appreciate your help with this. Which part of London are you from by they way?

Alex

you're welcome, have fun.

not quite London, a bit down south, hampshire.
and my home team is Spur, honest to god that the 'random' result of Arsenal 1-10 Tottenham was from excel's random() function.