Rank Scores and Average Ties

[philwood]

I have a problem that I can't work out how to resolve despite looking through several threads on several forums.
The program I am developing better suits Access, but the client requires it to be in Excel as that is what they are used to.

The program will generate leaderboards for several competitions, one of which requires points to be assigned based on the position in each event.

As an example, an event with 8 players would assign 8 points for 1st, 7 for 2nd down to 1 for 8th.
In the event of a tie, the players would receive an average of the points for the positions (i.e. for tied 2nd in a 8 player event, each player would get (7+6)/2 = 6.5 points).

What I want to do is calculate these 'Event Points' when I read the data in (the scores are all generated by an external app), so ideally I need to calculate them in code using an array of all the scores, but I am happy to throw it on a sheet if necessary.
The program reads in date, score and player which can be used for comparison.

I can deal with 2 ties easily, but not 3 or more (which is possible if unlikely)
Can anyone suggest how to achieve this please?

 

[Weaver]

So the date, score (I'm assuming that would be the rank) and the player name are 'read' in - how is the data supplied? Are player names already in the system? Is formatting and display critical? Do you have any sample data/expected output?

 

[philwood]

Thanks for the quick reply.

I've read the data in pretty much as I would in Access, so the list/table for the scores is in the following format:

PlayerID Date Score MPs EventPoints
1 12/03/09 5230 30 ?
2 12/03/09 5125 20 ?

etc

MPs is inconsequential for this.
I have stored Player Names in a separate list that I will reference for the final leaderboards.
Each Player has a unique ID which is listed in the above table next to their score.

The data is read in one event at a time (as each event has its own data store), and I have set it up to read the file (which is CSV format) into a textstream, convert to an array and then read the appropriate values into program variables.

The code then loops through all scores for the event taking the relevant data and adding it to the bottom of the list.
One additional thing - some events have 2 winners (effectively two separate events on the same date), however I have no flag for this in the list currently as it is inconsequential to final leaderboards.
Hence the reason to do it in code if possible, otherwise I will take each individual event and throw it on a separate sheet, do the EventScore calculations and copy it to the main table.

 

[barry houdini]

Hello philwood, is a formula approach any good to you?

I assume that the highest score will get most points.

If scores are in C2:C10 then this formula in D2 copied down to D10 will assign points on the basis you described:

=SUMPRODUCT((C2>C$2:C$10)+(C$2:C$10=C2)/2)+0.5

 

[philwood]

Thanks Barry!!

On first sight that appears to do exactly what I want. I just need to code it to write the formula for the appropriate number of scores in each event - which should be pretty darn easy!!

I'll get back to you if there are any issues, but I doubt there will be.