+ Reply to Thread
Results 1 to 6 of 6

Display last 10 games FORM win/draw/loss

  1. #1
    Registered User
    Join Date
    06-28-2014
    Location
    Male
    MS-Off Ver
    2007,2010
    Posts
    15

    Display last 10 games FORM win/draw/loss

    Hi Forum,

    Please help with the attached work book,I just need the so called FORM which is Win/Draw/Loss of the last 10 games displayed next to the team horizontally.
    The Wins/Draw/Loss for all the matches are formulated in columns J3:AC382 and conditionally formatted to fill the cells with red/grey and green.(At this stage of the season each team has played 3 games and the rest of the scores are generated randomly)
    I want the last 10 games FORM Win/Draw/Loss to be displayed horizontally next to the league calculation table.Lets say for Arsenal beginning with row AP3:AY3 for their last 10 games and so on for the other teams in the table,

    Cheers
    Als

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,476

    Re: Display last 10 games FORM win/draw/loss

    If you pre-fill all the results down to the bottom, then the last 10 games will be taken as from the end of the season.

    But, if you mean you want a snapshot at this point in time, then only 3 games have been played, so your FORM results can only display 3 values.

    I think it will be easier to display Home form and Away form separately (i.e. last five games of each), as you have the team names in different columns.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    06-28-2014
    Location
    Male
    MS-Off Ver
    2007,2010
    Posts
    15

    Re: Display last 10 games FORM win/draw/loss

    Hi Pete,

    to display Home form and Away form separately (i.e. last five games of each) for the last 10 fixtures is a good approach.
    Any help how could get this done please.Is there a formula to get the form ie W/D/L be displayed automatically once the scores are updated!
    Lets say its not the latest games, but for each game played and depending on the result a W/D/L could be updated next to the point table

    Thanks so much
    Al

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,476

    Re: Display last 10 games FORM win/draw/loss

    Hi Al,

    I've made a few minor changes to your workbook. I've removed the dummy results that you had below row 32 (well, I copied them to columns CD and CE first, in case you want to re-instate them), and I've changed the titles in cells AP2 to AY2 to reflect 5 home results and 5 away results. I've put this array* formula in cell AP3:

    =IFERROR(LOOKUP(INDEX($F:$F,LARGE(IF((Table1[Home Team]=Table2[[#This Row],[Teams]])*(Table1[HS]<>""),ROW($A$3:$A$382)),COLUMNS($A:A))),{0,1,3},{"L","D","W"}),"")

    and this similar array* formula in AU3:

    =IFERROR(LOOKUP(INDEX($G:$G,LARGE(IF((Table1[Away Team]=Table2[[#This Row],[Teams]])*(Table1[AS]<>""),ROW($A$3:$A$382)),COLUMNS($A:A))),{0,1,3},{"L","D","W"}),"")

    As you can see, the first formula is looking at the Home Teams and the Home Score, and the second one is looking at the away teams and scores.

    *Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

    The formulae can be copied across into the next 4 columns, and then all 8 cells can be copied down to row 22, to give the last five home results and the last 5 away results for each team. The latest result is always on the left of the block of five (i.e. h1 and a1), and you can see this in action if you enter a score for the next Arsenal home match on row 33. These will automatically adjust as the season progresses and you add more results.

    Hope this helps.

    Pete
    Attached Files Attached Files
    Last edited by Pete_UK; 09-05-2016 at 08:37 PM.

  5. #5
    Registered User
    Join Date
    06-28-2014
    Location
    Male
    MS-Off Ver
    2007,2010
    Posts
    15

    Re: Display last 10 games FORM win/draw/loss

    Thanks Pete,That was a quick fix for my request.Have a good day

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,476

    Re: Display last 10 games FORM win/draw/loss

    Glad to help, Al.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pool competition DRAW with alternate home/away games
    By gazzal in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 05-15-2015, 06:15 AM
  2. [SOLVED] Win Loss Draw streak for last 10 games for ladder
    By steggspolak in forum Excel General
    Replies: 18
    Last Post: 05-23-2014, 06:29 AM
  3. Replies: 7
    Last Post: 08-03-2013, 09:51 AM
  4. Replies: 0
    Last Post: 08-09-2012, 07:56 PM
  5. Rank a Win/Loss record by games played
    By JO505 in forum Excel General
    Replies: 2
    Last Post: 10-29-2011, 07:36 PM
  6. Win/Loss/Draw statistic
    By acedabrain in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2011, 09:12 AM
  7. Win Loss Draw formula
    By luk in forum Excel General
    Replies: 4
    Last Post: 10-18-2009, 09:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1