Ranking & Scoring Issue

[k2sul]

Hi All,

I'm having an issue with a league spreadsheet that I'm trying to create. I have searched high and low and three+ hours later I'm not much wiser. The answer may be simple, but I just can't see it for looking.

Anyway, the problem...

The league is split into divisions and there are usually 10 competitors per division. Lets focus on a single division though for now.

Name Card1 Card2 Score Points

shooter 98 97 195 6
shooter 96 99 195 6
shooter 95 100 195 6
shooter 97 98 195 6
shooter 98 98 196 7
shooter 99 95 194 2
shooter 100 97 197 8
shooter 100 100 200 10
shooter 100 100 200 10
shooter 97 95 192 1

The score column is just the sum of columns Card1 & Card2

In the manual world of scoring this league, the highest score gets 10 points. If there are two shooters with the same high score, they both get 10 points. However in the above example the next highest score will get 8 points because the theoretical 2nd highest place earning 9 points has been eliminated from the calculation. This continues until all shooters have been allocated a score.

If all the scores were different, the calculation would be easy - RANK could take care of it for me, however the way RANK handles duplicates is just not suitable. I'm using Excel 2013 and Im not finding the RANK.AVG or RANK.EQ to be much help either.

I would be grateful for a nudge in the right direction on this.

League.xlsx

Many thanks

[benishiryo]

welcome to the forum, k2sul. try this in F4:
=COUNTIF($E$4:$E$13,"<="&E4)

[k2sul]

Many many thanks Benishiryo, works perfectly & so much simpler than the convoluted mess I was getting myself into.

[martindwilson]

if there are eleven teams what does the 11th get? 1 or 0
also you say 10 points for leader for 11 teams benishiruos formula gives 11 points

[k2sul]

if there are eleven teams what does the 11th get? 1 or 0
also you say 10 points for leader for 11 teams benishiruos formula gives 11 points

Hi Martin,

The max score awarded is equal to the number of entrants. So for 11 teams, the max high score is 11 with the lowest scoring team getting 1 point, or for 9 teams the max high score is 9 etc.

In this instance Benishiryos solution works perfectly, and handles variable numbers of competitors.