+ Reply to Thread
Results 1 to 13 of 13

Formula for next upcoming fixture

  1. 09-15-2024, 06:14 PM #1
    speedychaz94
    speedychaz94 is offline
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    32

    Question Formula for next upcoming fixture

    Hi there,

    It's been a while since I have posted here. I've had great help in the past so fingers crossed the same can be said for this time.

    Anyway, I have a fantasy premier league league which I am the league admin of. This season we are doing a head-to-head format which is posing new challenges. I have created an 'H2H fixtures' sheet with all fixtures 38 Gameweek (GW) matches for all 84 players. Only 3 GWs have been played. I would like is to have the next fixture for each player in P4:P87 in the H2H Standings sheet. Can someone help? This is beyond my Excel skills.

    Bonus points if able to colour-code the upcoming fixture with fixture difficulty, based on player rank.

    I hope this makes sense. Let me know if not.

    Thanks in advance.

    *Edit - I have also posted this on another forum - mrexcel.com/board /threads/ formula-for-next-upcoming-fixture.1264388/

    Fantasy 2425 H2H sample.xlsx
    Last edited by speedychaz94; 09-16-2024 at 06:52 AM. Reason: Not included link for same question posted on another forum
    Reply With QuoteRegister To Reply
  2. 09-16-2024, 01:33 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,919

    Re: Formula for next upcoming fixture

    I would like is to have the next fixture for each player in P4:P87 in the H2H Standings sheet.
    What do you want to appear there? The week number? Their opponent?

    Bonus points if able to colour-code the upcoming fixture with fixture difficulty, based on player rank.
    How would I know what the difficulty is? How do you want the colour coding to work? More definition, please.

    You will know from asking for help here before that we expect you to mock up manually a few rows of data as you are hoping to see it.
    Last edited by AliGW; 09-16-2024 at 01:45 AM. Reason: Typos fixed.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 09-16-2024, 01:42 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,919

    Re: Formula for next upcoming fixture

    If you want the name of their next opponent, try this in P4 copied down:

    =LET(f,FILTER('H2H Fixtures'!$B$3:$EW$44,'H2H Fixtures'!$B$2:$EW$2=C4+1),IFNA(XLOOKUP(B4,INDEX(f,,1),INDEX(f,,4)),XLOOKUP(B4,INDEX(f,,4),INDEX(f,,1))))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-16-2024, 03:59 AM #4
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Formula for next upcoming fixture

    Non-365 formula, copy down:
    =IFERROR(INDEX('H2H Fixtures'!$B$3:$EW$44,MATCH(B4,INDEX('H2H Fixtures'!$B$3:$EW$44,,4*C4+1),0),4*C4+4),INDEX('H2H Fixtures'!$B$3:$EW$44,MATCH(B4,INDEX('H2H Fixtures'!$B$3:$EW$44,,4*C4+4),0),4*C4+1))
    Reply With QuoteRegister To Reply
  5. 09-16-2024, 04:22 AM #5
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Formula for next upcoming fixture

    To spill the formula, change B4 to B4:B87 for both Ali's and my formulas.
    Reply With QuoteRegister To Reply
  6. 09-16-2024, 05:19 AM #6
    speedychaz94
    speedychaz94 is offline
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    32

    Re: Formula for next upcoming fixture

    This works an absolute treat. Thank you so much.

    I am sorry for the lack of information/mock data. I am a little rusty.

    The difficulty would be based on their league rank (cells A4:A87) and the colour could be red for 1-10 positions, orange for 11-30, grey for 31-60, and green for 61-84.

    I might be able to figure this out though.

    Kind regards,
    Reply With QuoteRegister To Reply
  7. 09-16-2024, 05:20 AM #7
    speedychaz94
    speedychaz94 is offline
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    32

    Re: Formula for next upcoming fixture

    Quote Originally Posted by AliGW View Post
    If you want the name of their next opponent, try this in P4 copied down:

    =LET(f,FILTER('H2H Fixtures'!$B$3:$EW$44,'H2H Fixtures'!$B$2:$EW$2=C4+1),IFNA(XLOOKUP(B4,INDEX(f,,1),INDEX(f,,4)),XLOOKUP(B4,INDEX(f,,4),INDEX(f,,1))))
    This works an absolute treat. Thank you so much.

    I am sorry for the lack of information/mock data. I am a little rusty.

    The difficulty would be based on their league rank (cells A4:A87) and the colour could be red for 1-10 positions, orange for 11-30, grey for 31-60, and green for 61-84.

    I might be able to figure this out though.

    Kind regards,
    Reply With QuoteRegister To Reply
  8. 09-16-2024, 05:35 AM #8
    speedychaz94
    speedychaz94 is offline
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    32

    Re: Formula for next upcoming fixture

    Quote Originally Posted by josephteh View Post
    Non-365 formula, copy down:
    =IFERROR(INDEX('H2H Fixtures'!$B$3:$EW$44,MATCH(B4,INDEX('H2H Fixtures'!$B$3:$EW$44,,4*C4+1),0),4*C4+4),INDEX('H2H Fixtures'!$B$3:$EW$44,MATCH(B4,INDEX('H2H Fixtures'!$B$3:$EW$44,,4*C4+4),0),4*C4+1))
    This also works.

    Couple of excel magicians

    Many thanks
    Reply With QuoteRegister To Reply
  9. 09-16-2024, 06:36 AM #9
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2508 Win 11
    Posts
    24,940

    Re: Formula for next upcoming fixture

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
    For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

    Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

    (Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  10. 09-16-2024, 06:37 AM #10
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,111

    Re: Formula for next upcoming fixture

    Also:

    https://www.mrexcel.com/board/thread...xture.1264388/
    Reply With QuoteRegister To Reply
  11. 09-16-2024, 06:54 AM #11
    speedychaz94
    speedychaz94 is offline
    Registered User
    Join Date
    12-05-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    32

    Re: Formula for next upcoming fixture

    Quote Originally Posted by alansidman View Post
    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
    For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

    Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

    (Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
    I am sorry about this. I have now rectified and done what you have requested.

    Thanks
    Reply With QuoteRegister To Reply
  12. 09-16-2024, 07:32 AM #12
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2019
    Posts
    4,542

    Re: Formula for next upcoming fixture

    Quote Originally Posted by josephteh View Post
    To spill the formula, change B4 to B4:B87 for both Ali's and my formulas.
    Just noticed, the spill formula is already in Ali's workbook!
    Reply With QuoteRegister To Reply
  13. 09-16-2024, 02:55 PM #13
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,919

    Re: Formula for next upcoming fixture

    The SPILL formula won't work in a structured table. though.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Looking for formula that filters upcoming due dates that haven't been filed
    By thematrix05 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-01-2023, 09:26 PM
  2. [SOLVED] Looking for formula that shows upcoming due dates for different types of forms
    By thematrix05 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2023, 07:51 PM
  3. [SOLVED] Formula/Conditional formatting to highlight upcoming oil change week
    By Zahid0111 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-07-2022, 04:46 PM
  4. Formula to get the upcoming pay date
    By lucid65 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-11-2021, 09:32 PM
  5. [SOLVED] Show upcoming date - Excel formula
    By Sinep D in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2015, 06:08 PM
  6. [SOLVED] How to calculate lamps per fixture based on lamp type & lamps per fixture?
    By Sandcastle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-27-2015, 04:09 PM
  7. [SOLVED] football formula Fixture table help
    By Paul T in forum Excel General
    Replies: 2
    Last Post: 09-04-2005, 03:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1