League Table Formula Problem

Brian Thomas

New member
Joined
Nov 27, 2012
Messages
30
Reaction score
0
Points
0
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.

View attachment Old Town Poker Season 12 League Table_beta.xlsx

Many thanks,

Brian.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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),"-")
 

Brian Thomas

New member
Joined
Nov 27, 2012
Messages
30
Reaction score
0
Points
0
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.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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!

:)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,524
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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. ;)
 

Brian Thomas

New member
Joined
Nov 27, 2012
Messages
30
Reaction score
0
Points
0
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.
 
Top