• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A Problem With Rankings

G

GreenLakeNick

New Member
I’m using Excel to track player finishes and award points based on those finishes and am stuck on how to handle ties.


There are ten players. I can rank their finishes 1-10 using Excel’s rank function and it handles ties. Thus, if there are ten players the finishes can be 1, 1, 3, 4, 5, 6, 6, 6, 9, 10, where there was a two-way tie for first and a three-way tie for sixth.


The problem arises in the next step. I have to award points based on these finishes. If I key the points formula off the ranking, it would be 10, 10, 8, 7, 6, 5, 5, 5, 2, 1.


The problem – and this is where I’m stuck – is that isn’t the correct way to do it. The correct result would have the first two guys splitting first- and second place points – (10+9)/2 = 9.5 points each instead of ten – and the three-sixth place guys splitting sixth-, seventh- and eighth-place points – (5+4+3)/3, or four points each instead of five.


Any idea how to do that?
 
Hi, GreenLakeNick!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


If you can afford to spent a couple of helper columns, give a look at this file:

https://dl.dropbox.com/u/60558749/A%20Problem%20With%20Rankings%20%28for%20GreenLakeNick%20at%20chandoo.org%29.xlsx


Just advise if any issue.


Regards!
 
Hi,

Here is a formula you could try:


assuming your ranked list is in A1:A10

Put the following formula in cell B1 (or choose another starting cell) and copy down


=SUMPRODUCT(($A$1:$A$10=A1)*{10;9;8;7;6;5;4;3;2;1})/COUNTIF($A$1:$A$10, A1)


Hope this helps.


Cheers,

Sajan.
 
OMG, that worked!! Except for one slight thing. That 10-person example I gave was just an example. In some cases there will be 63 players, in another 9, in another 14, etc. Is there some way, in the second part of the equation, to put a range instead of "10, 9, 8, 7 ...1" I'm trying to come up with a formula I can just copy and paste and not have to type in "65, 64, 63, 62 ... "


Thanks, Nick
 
I stand corrected. It didn't work. :-( When I listed the players in order, so that 10 was in cell A1, 9 was in cell B1, etc. down to the tenth guy in J1 it worked fine. However, when I mixed them up so that the 10-point guy was in C1, the second place (9 point) guy was in F1, etc. it didn't work. Realistically it's going to be the latter 99.999 percent of the time.
 
Hi, GreenLakeNick!

Are you talking about Sajan's formula or about my uploaded file? Anyhow, would you please upload a sample file? Thank you.

Regards!
 
GreenLakeNick


Firstly, Welcome to the Chandoo.org forums.


As SirJB7 mentioned above, reading the 3 Green Sticky posts explains how the site works and how to upload files


Here is a shortcut to the relevant section: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Nick,

Can you elaborate on your requirements? My previous formula was based on your original requirements. Sounds like there are a few additional conditions...


Are you looking to have your player rankings in rows or columns?


Can you elaborate on what you mean by "mixed them up"? Will your data and points no longer match a straight sequence? if not, we can include a lookup as part of the formula, instead of the straight numeric sequence. for example, if you have 15 players, you can setup a range on your worksheet to indicate how many points each rank would get.


By the way, to create a sequence of numbers in rows, you could use something like

=66-row($a$1: index(a:a, 65))


The above results in {65;64;63;.....1}


To get a sequence in columns, you could try

=66-col($a$1:index(1:1,65))


(please check if the function is COL or COLUMN. Since I am away from Excel, I wont be able to check for a few hours.)


-Sajan


EDIT: It's COLUMN (SirJB7)
 
Hi, GreenLakeNick!

Download again my updated file from same previous link.

Same two helper columns, it now handles dynamic and unsorted participants and prizes.

Just advise if any issue and upload your file with proper detailed explanations. Thank you.

Regards!
 
You must log in or register to reply here.
Back
Top