Pub Poker League Table Help

[Whirlers]

Hiya all
Only just found this forum after trying for weeks to workout the right formula for this league table, hopefully somebody on here can help.

I run a pub poker league, and what i need is automated table that works out the leaderboard, but by only adding their top 20 scores out of all their score's entered.
So on the first page i will have all the names of the players and each week i will enter their score for that week, upto i think around 45 weeks in the year. While on the 2nd page a table will form working out the leaders of the game by adding their top 20 score's ONLY out of the possible 45 weeks. Also a average of their top 20 score's would be a nice addidtion to the table if this can be achieved.

If anyone can help and would like to contact me personally, please feel free to email me at whirlers@blueyonder.co.uk, or if you need anymore info
Any help is much appreciated
Thx
Graham

[Cheeky Charlie]

Hi, not sure of your layout, but:

Use large(results,rank) to return the 1st best, 2nd best, 3rd best results, up to 20 then sum or average these numbers. There are two ways (that I can see) of getting the references to 1-20 into the formula, write them into an embedded array - {1,2,3,4...20} or create an array formula which arbitrarily produces the same - {row($1:$20)} (this is written without the curly brackets, then entered with ctrl + shift + enter - which adds the brackets).

So you get formulae like this:
{=SUM(LARGE(C4:AU4,ROW($1:$20)))}
or
=SUM(LARGE(C5:AU5,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}))

and for averages:
{=AVERAGE(LARGE(C4:AU4,ROW($1:$20)))}

Array formulae aren't recommended (slow down Excel), and aren't needed here, but still, it's quite clever and quite a bit neater...

See attached, I've guessed at your layout, but the principles will apply however you've done it. Not sure if you really need to have a second summary table, you could just use something similar to the group I've created to hide/show the detail of results - makes it easier to use if it's just one table (even if it's a bit big).

HTH

PS Don't be tempted to use average(if(results>21st largest result)) logic, if your 20th and 21st results are the same this would return a too-low sum and a too-high average!

[Whirlers]

really appreciate your help on this, i am pretty new to excel so most of what you have said has gone completely over my head. I will howver dabble with my saved file and your formula's and hopefully get somewhere.

It sounded pretty simple until i saw the formula's.....lol

[Whirlers]

I have attatched my simple layout, which i will configure once the formula is working, if you can adjust my setup to work i would be most greatful

[Cheeky Charlie]

Your layout is exactly as I guessed, which is nice - just put the formulae in and point it at the right ranges (i.e. replace C5:AU5).

You shouldn't need anything else; but let us know if you're still stuck. If it is, please mark the thread as solved.

CC

[Whirlers]

Sorry i must be doing something wrong, unless it because i'm using 2007 but i doubt it.
I'm entering your code and changing the cells to mine, but i'm getting a error code invalid number. Might be the fact i have 2 different pages, one with the names & score's and the other with the league table so the formula isn't matching.

I'm trying to copy & paste the cells but when i alter the rows mine is just showing the biggest number.
Sorry about this, this is probally really simple....i'll have another bash in the morning
thx

[Whirlers]

Ok i think i have it sorted now, the problem was it was bringing up a error for all the empty boxes in a players row. If i enter 0 in all the yet to play weeks the formula seems to work.

Only problem now is my games played formula, is saying players have played all games instead of just the one's with actual score's in. I was gonna enter NP in the weeks players didn't play, but this will probally error the 20 games formula then....lol

What i need is a SUMIF formula for the games played i think, so it only picks up the games with score's more than 0

Will continue to crack on, although 2am now....time for bed

[Whirlers]

Sorry me again, if i delete all the numbers from a players row i get the NUM error, it only disappears when at least 20 scores have been added, or like i said....i have to 0 every box

[Whirlers]

No i thought i had it, but it all seems to be wrong, the average doesn't seem to be right at all either.

If you have only played 3 games, than your total score needs to be divided by 3 to get your average, if you played 6 it should be divided by 6 and so on, until you reach your top 20 score's which will be divided by 20. I think because i have to zero every box it's assum ing they have played 48 games and averaging it on that.

It may be because it pretty late, i'll have another look tomorrow....def off to bed now

[Cheeky Charlie]

wow...

How about this:
=SUM(LARGE(B2:AW2,ROW(INDIRECT("$1:"&COUNT(B2:AW2)))))
Ctrl+Shift+Enter

Not sure where to get to formula auditing in 07 but ask help for "evaluate formula" it shows you the calculation steps.

HTH

PS average can just be sum/count(range)

[Whirlers]

No good, if i enter it like that i get a invalid cell ref error once again unless i 0 all the boxes, and when i do enter the points it only displays the top score.

I appreciate your help on this but my head hurts now....lol

[Cheeky Charlie]

Ctrl+shift+enter

[Whirlers]

i saw the ctrl, shift & enter on your reply but couldn't workout what you meant by it, if i highlight the box i need the forumla in and press ctrl, shift & enter i got nothing.

Like i said i'm pretty new to excel but i do appreciate all your time on this, and thx for the new sheet i will have a mess with that in a bit
Thx
graham

[Whirlers]

Ok this seems to be working fine, really appreciate that
Only thing is the averages

It seems the averages are worked out by dividing the total points by the games played, the only problem with this is the total points are only based on there top 20 score's, so if you divide this by the games played and you have played more than 20 games it will bring back a below average....average if you see what i mean

Will the code you used above for averages work for this method ?

[Cheeky Charlie]

mmm, good point, use

min(20,count(scores))

[Whirlers]

At the moment the averages are setup with this formula =AY2/AX2
If i delete this formula and replace it with min(20,count(scores)) it brings back a 0 result in the averages box

I must really be doing your head in now, so i apollogise for my ignorance in these matters.....

Also the formula for Graham is working fine for that player, to make it work for the rest of the players can i just copy & paste the formula

=SUM(LARGE(B2:AW2,ROW(INDIRECT("$1:"&AX2))))

but change the B2:AW2 & $1:"&AX2 to the next row down, changing all numbers to 3 ?? When i try this i get REF error so obviously not. What am i doing wrong here

[Cheeky Charlie]

Crikey, you are a novice!

AY2={SUM(LARGE(B2:AW2,ROW(INDIRECT("$1:"&MIN(AX2,20)))))}

If you select these cells (AX:AZ), there will be a little black box in the bottom-right corner of the selection. Drag that downwards with the mouse.

CC

[Whirlers]

I think novice is a little advanced for me, i have only had Office for 3 weeks
This may have been a little too complicated a first task i'm thinking

I have just got around to really testing this out, and it's still not working.....even when i download you file you uploaded it's allowing more than 20 results to be added. I used your form and added 20 results of 100, and then added 1 more of 50 which shouldn't be added to the total due to it only using the top 20 score's but it is adding it on my form.

Is this because i'm opening a 2003 document on a 2007 version of excel ? Has the formula not converted properly. Also can you explain what you mean when you say use ctrl, shift & enter - to enter a formula

I'll drop this question in the excel 2007 forum, incase it's a compatability issue

[Whirlers]

All sorted now, had a slight calulation wrong in the form that was throwing the rest of the colums out, thx for all your help, can i give you some rep or feedback ?

[mudraker]

Whirlers
Your ;ast rep;y indicates that this problem is Solved

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[Whirlers]

Whirlers
And please never edit a thread in which someone else has responded.

No idea what your taking about here, i have only edited my posts, due to spelling mistakes if you can't edit your own posts why is there a edit button ??

The reason i posted in the other thread is this forum is for excel 2003 and i have 2007 and was having problems with some of the suggestions this helpful chappy was giving me, so i reposted in the other forum incase it was a compatability issue.

Nice way to make someone feel welcome by spouting code's & infractions at them for asking for help, some ppl just love the power trip i guess.

thx for your help CC, i think i'll find a forum that isn't ran by nazi's

[Cheeky Charlie]

It would be a shame to go, I can assure you mudraker has a great attitude. The reality is that in order to keep the forum 'clean' there has to be quite a strict approach to moderating - it takes a lot of time and it's not always possible to read thoroughly a (20 post!) thread. On this occasion, perhaps the moderation wasn't quite right (and the explanation not entirely clear?) - give them a chance to fix it.

Also, for what it's worth, I came here because I didn't like the over-zealous approach to moderation on another forum (some people will know exactly what I mean).

Brgds,
CC