Rank Scores and Average Ties

philwood

New Member
Joined
Apr 15, 2009
Messages
5
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Apr 15, 2009
Messages
5
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

New Member
Joined
Apr 15, 2009
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top