Ranking function
 

I'm tracking Nascar stats in a workbook consisting of several sheets. I'm manually entering all data consisting of drivers starts, finishes, wins, etc. The issue I'm having is ranking the drivers by several categories, one beings laps led. The formulas I have seem to work with the exception that when two or more drivers have led the same number of laps, it lists one of the drivers twice. Right now, as an example, one charts reads:

Rank Driver # Laps Led
17 Sam Hornish 36
18 Marcos Ambrose 35
18 Marcos Ambrose 35
20 AJ Almendinger 33

At this point in the season M Ambrose and M Kenseth are tied for leading 35 laps. I would like the 3rd name in the list to read M Kenseth.

On my sheet I have column B setup with drivers names, column C with laps led. My Laps leaders charts consists of 4 columns. The 1st column (AD) is for whose led most laps (descending order) with formula: =IF(AF2=0,"",RANK(AF2,$AF$2:$AF$21)) - 2nd column is name of driver (AE) with formula =INDEX(B:B,MATCH(LARGE($C:$C,ROWS($A$1:A$1)),$C:$C ,0)) - 3rd column (AF) lists total laps led & formula =INDEX(C:C,MATCH(LARGE($C:$C,ROWS($A$1:A$1)),$C:$C ,0)) - 4th & final column I put in what I thought might be a tiebreaker column (AG) with formula =IF(C2="","",C2+ROW()/10^10.

I'm not having any luck with the formulas relisting the drivers names properly. I don't know if I'm overdoing it with the formulas that I have. I'm thinking I might need to use the CounIF function but I'm so lost right now I'm not sure How it needs to be used.

Any help would be greatly appreciated.

Doug

Could you attach a sample of your workbook here?

Yes, I'd be delighted to. The issue I'm having is located on the Miles Leaders Tab. Columns AD to AL consists of one of four categories that I' trying to figure out.

Hi,

Have you tried just sorting the columns. No Formulas needed. Double sort will give "equal" drivers in Alpha order.

Educo

No, I never thought of that. I'd have to rearrange some small charts but will give it a whirl. Thank you

The Column to the left consists of the drivers names (not all of them listed due to space limitations; set up as column B2) and next column (C2) showing number of laps led. The Chart to the right I wish to sort the top 20 drivers with the most laps. The 18th place driver is showing up twice, although he is tied with another driving for same # of laps led.

The Ranked column (AD) I have the formula =IF(AF2=0,"",RANK(AF2,$AF$2:$AF$21)). The column with the drivers names (AE) I have the formula =INDEX(B:B,MATCH(LARGE($C:$C,ROWS($A$1:A$1)),$C:$C ,0)). The number of laps led column (AF) I have formula =INDEX(C:C,MATCH(LARGE($C:$C,ROWS($A$1:A$1)),$C:$C ,0)).

I hope the image I attached is enough to go by. I know the website will not allow me to send the entire file but hope this will help. I appretiate any assistance in the matter. For any further questions or information please let me know.

Thank you for your time.

Doug

Since there is a tie in your table, I would suggest to use this formula for your RANK function to prevent duplicates.

=RANK(AF2,$AF$2:$AF$21)+COUNTIF(AF2:$AF$21,AF2)-1