Ranking a leaderboard with ties without missing numbers

[rosters]

Hi,
I hope i have posted this question in the right place i've never posted before. I have made a football prediction sheet. The data is inserted into the setup sheet and on another worksheet i have a leaderboard. The names and scores are moved from the setup sheet to the leaderboard sheet. I want to have the leaderboard to have rank,player,score in separate columns with the person with the most points at the top and the person with the lowest points at the bottom, but i want to also have people with the same score in the same position so if 2 people are 1st they are both in position 1 on the leaderboard with the next person ranked at position 2 not 3. I want this to work even if there are 4 people at 1st position, the 5th person would be ranked 2nd. I have attached how far i have got so far. any help would be greatly appreciated as i have hit a brick wall with this i have tried a few different formulas but nothing i've tried seems to work thanks.Football_Prediction.xlsx

[martindwilson]

have a look here in the last section
http://www.tushar-mehta.com/excel/ne...ing/index.html
also look at rank unique
then use unique rank to look up the continuous rank
example coming up

[micope21]

Hi

Click on workbook from martinwilson. Then J2 cell put in =SUMPRODUCT((H$2:H$20>$H2)/COUNTIF($H$2:$H$20,$H$2:$H$20&""))+1 then copy down.

Something not right in column I
Reason if you change Cell B17 to 1. You will notice my formula is correct to what martinwilson formula on column I.

Rest column A to H spot on.

[martindwilson]

there was the wrong formula in i2 and also d2 was blank .my bad

[icestationzbra]

just another approach from the following thread:

http://www.excelforum.com/excel-form...al-number.html

Please Login or Register  to view this content.