Results 1 to 6 of 6

Thread: League Table Formula Problem

  1. #1

    League Table Formula Problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I was wondering if anyone could just help tweak my league table a little.

    The poker league I run has been growing and I have raised the maximum number of players on any given night from 27 to 30 so I have had to adjust the points allocation and payout structure. I've managed to update the spreadsheet to reflect most of these changes but I can't work out how to get the formula (contained in the salmon pink cells in the table to the right of the results table) to add the winnings from the 5th place finishers to the "Earnt" column in the main table. I'd be really grateful if anyone could add that function to the formula for me.

    Old Town Poker Season 12 League Table_beta.xlsx

    Many thanks,

    Brian.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Formula for AD4 (then copy down and across to cover AD4:AM47)

    =IFERROR(IF(INDEX($I4:$AB4,MATCH(AD$2,$I$2:$AB$2,0)+1)>=LARGE(INDEX($I$4:$AB$53,0,MATCH(AD$2,$I$2:$AB$2,0)+1),5),LARGE(INDEX($I$55:$AB$61,0,MATCH(AD$2,$I$2:$AB$2,0)),COUNTIF(INDEX($I$4:$AB$53,0,MATCH(AD$2,$I$2:$AB$2,0)+1),">="&INDEX($I4:$AB4,MATCH(AD$2,$I$2:$AB$2,0)+1))),0),"-")
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Hi Ken,

    Thanks for that, it's much appreciated.
    I've copied it across all the cells and now the "Earnt" column shows the correct values.

    Cheers,

    Brian.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    780
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Ken Puls View Post
    Formula for AD4 (then copy down and across to cover AD4:AM47)

    =IFERROR(IF(INDEX($I4:$AB4,MATCH(AD$2,$I$2:$AB$2,0)+1)>=LARGE(INDEX($I$4:$AB$53,0,MATCH(AD$2,$I$2:$AB$2,0)+1),5),LARGE(INDEX($I$55:$AB$61,0,MATCH(AD$2,$I$2:$AB$2,0)),COUNTIF(INDEX($I$4:$AB$53,0,MATCH(AD$2,$I$2:$AB$2,0)+1),">="&INDEX($I4:$AB4,MATCH(AD$2,$I$2:$AB$2,0)+1))),0),"-")
    Something about needles and haystacks comes to mind!


  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    LOL!

    Actually this one wasn't so bad. I figured it had something to do with one of the LARGE statements, and one was capping at 4. Once I flipped that to 5 it was just a matter of figuring out which index it was actually pointing to. Admittedly, at that point it got a bit forensic.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Glad to hear to enjoyed the challenge... which was light years ahead of my understanding!
    Like you though I do get a thrill out of getting Excel to perform some otherwise tricky task and present it to me neatly in a cell, it's just my attempts are rather simple by comparison to yours.

    Brain.

Posting Permissions

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