• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Football - Identifying games using historical data that fulfill Form criteria.

Widgewilliams

New Member
Hi guys and gals.

At this stage Im not looking for a specific solution to a problem but rather ideas on how to approach something.

I have several spreadsheets of football data covering several seasons of results and odds.

eg:

DateHomeTeamAwayTeamHome GoalsAway GoalsResultHome OddsDraw OddsAway OddsHA
18/08/12ArsenalSunderland00D1.444.899.5DD
18/08/12FulhamNorwich50H1.853.824.8WL
18/08/12NewcastleTottenham21H2.73.472.85WL
18/08/12QPRSwansea05A2.13.554.2LW
18/08/12ReadingStoke11D2.453.43.26DD
18/08/12West BromLiverpool30H4.383.612WL
18/08/12West HamAston Villa10H2.33.463.51WL
19/08/12Man CitySouthampton32H1.217.5519.38WL
19/08/12WiganChelsea02A6.7541.67LW
20/08/12EvertonMan United10H4.453.711.91WL
22/08/12ChelseaReading42H1.296.214WL
25/08/12Aston VillaEverton13A3.253.42.6LW
25/08/12ChelseaNewcastle20H1.534.557WL
25/08/12Man UnitedFulham32H1.335.5212WL
25/08/12NorwichQPR11D2.383.523.25DD
25/08/12SouthamptonWigan02A2.23.533.65LW
25/08/12SwanseaWest Ham30H2.153.473.85WL
25/08/12TottenhamWest Brom11D1.544.537.5DD
26/08/12LiverpoolMan City22D3.153.452.48DD



Data extends up to 3000 rows for some leagues.

Somehow, I would like to do two similar things.

The first is to identify only those games where the home team has WON 4 out of their 5 PREVIOUS home games, AND where the away team has LOST 4 out of PREVIOUS last 5 away games.

Specifically any game where the home teams previous form is WWWWD, WWWDW, WWDWW, WDWWW, DWWWW. The away team form combination is abit more complex with 112 possibilities but basically no more than 1 win in their last 5 games

While the second is to identify only those games where there have been 2 or more goals in 4 of the home teams previous 5 games, AND 2 or more goals in 4 of the away teams previous 5 away games.

Is this something that is even possible in Excel?

If it is, I'd really appreciate some general advice on how you would go about approaching the task?
 
Last edited:
My original idea would be to list every team and extract the form for every game for both home and away

E.g.. Southampton (home) - WLDWWLLLLDWWWW.......LDLL

And then manually try to FIND the combinations I need from the home team and then the away team, then see if there's any games played between them where those combinations match.

Which is a daunting task with over 30 leagues averaging 20 teams and 1500 games per league.
 
Hi Widgewilliams , there was not enough data available in your example, so I went with some copies of the same table.
Please do upload a sample workbook in the future with enough data. Saves us some tedious work in making the example ourselves.

See attached if that is something that looks suited or not?
 

Attachments

  • draw-won-lost-last5games.xlsx
    19.1 KB · Views: 3
Hi Widgewilliams , there was not enough data available in your example, so I went with some copies of the same table.
Please do upload a sample workbook in the future with enough data. Saves us some tedious work in making the example ourselves.

See attached if that is something that looks suited or not?

Hi G

Thank you SO much for what you have done. It isn't really what I am wanting to do, although it would actually be perfect if it isnt possible to do what I want to accomplish. I am grateful. If I cant achieve my initial goal then I could definitely use what you have shown me to identify upcoming games who fit my criteria.

My apologies for the lack of data. Ive attached a complete sheet now with your work included.

Ideally, what I am wanting to do is identify past games where the criteria have been met. I'd like to be able to pull from the data those games that when played met the criteria I'm after.

So, as an example. Lets assume that row 405 Man City vs Hull fits the bill and Man City had form WWWDW and Hull had form LLLDL prior to that game then this would be a qualifier and would be pulled to a new sheet. Lets also assume that row 978 Tottenham Hotspur vs Sunderland also fit the bill and this was also pulled to the new sheet.

The new sheet would then display only those games played where the home teams form and the away teams form prior to that game met the criteria and would look something like this.

31/08/13Man CityHull
2​
0​
H
1.2​
8.52​
20.56​
WWWDWLLLDL

17/01/15TottenhamSunderland
2​
1​
H
1.65​
4.3​
6.4​
WWDWWLLDLL

As stated I don't know if that is something that can be done, and it would be a big ask to expect anyone to do the work for me but all and any suggestions, pointers, advice etc of what needed to be done would be great.
 

Attachments

  • G Chandoo.xlsx
    287.8 KB · Views: 1
Last edited:
OK, ive used what you've shown me to get halfway there, i think. You'd taken me a step further than we actually needed to go :)

I applied it to every game in the data, extended it to apply to the previous 6 games and then removed the last game to get the form for the last 5 results for each team.

Now, what I need to do is pull all those games where there is a YES in column U into a seperate sheet (columns B-R). I'm pretty certain I need to use an array to do it. I'm not exactly sure what I need to do, but I'm feeling alot more positive that it can be done.
 

Attachments

  • G Chandoo.xlsx
    724.2 KB · Views: 2
I don't need to explain it, as some-one else did already a fantastic job doing so in a video clip.
Try google "Excel 2010 Magic Trick 670: AGGREGATE Function Array Formula #4 Extract Records 1 criterion" of Mike Girvin aka Excel Is Fun.
EDIT: or his trick 1346 as it is using table structured references as I did.
 
Last edited:
I couldn't resist looking at this using Office 365 insider version for dynamic arrays.
One formula typed into a single cell is all it takes!

= FILTER( Table1[[Date]:[A]], Table1[Qual]="Yes")

60568
 
Guido
I didn't achieve a single spilt formula but COUNTIFS took me pretty close.
My formula for the 4 home wins out of the last 5 is
= COUNTIFS(Results[HomeTeam], currentTeam, Results[Date], ">="&CutoffH, Results[H],"W") >= 4
and for away losses
= COUNTIFS(Results[AwayTeam], currentTeam, Results[Date], ">="&CutoffA, Results[A],"L") >= 4

That would be all very sweet if the cut-off dates (5th matches from the end) were calculated as arrays but my formulae
= LARGE( IF( Results[HomeTeam] = currentTeam, Results[Date] ), 5 )
= LARGE( IF( Results[AwayTeam] = currentTeam, Results[Date] ), 5 )

do not handle an array of teams. I could output the resulting dates to a helper range and introduce a new name for the entire array but, instead, I stuck with working the formula for each team individually. At least with CutoffH and CutoffA as named formulae I avoided the use helper cells. They are fine if they contain interesting information but that wasn't the case here.

The formula that did help was that to get the list of distinct teams
= SORT( UNIQUE(Results[HomeTeam]) )
 
I don't need to explain it, as some-one else did already a fantastic job doing so in a video clip.
Try google "Excel 2010 Magic Trick 670: AGGREGATE Function Array Formula #4 Extract Records 1 criterion" of Mike Girvin aka Excel Is Fun.
EDIT: or his trick 1346 as it is using table structured references as I did.

GraH - Guido, thank you so much for your help with this. I've now played with table structured references and the aggregate function array formula, going forward I think the latter will be more useful due to just being able to replace the data and having it update automatically and then saving renaming the file as a different league.

Peter, from the looks of it I could do with upgrading my version of excel :)

Both, I'm enjoying reading your conversation while trying to figure out what you are both on about :p Thanks guys.
 
Back
Top