Forum Discussion
ranking athletes based on best time
I have a track and field spreadsheet and I need to rank the athletes best times against each other but excluding their other times.
ex. I have 3 athletes that have each ran the distance 3 separate times, so 9 entries total for that distance. I need to rank them 1,2,3 based on their best times and excluding there slower times.
Thanks
2 Replies
You can use the RANK function in Excel to rank athletes based on their best times.
The RANK function returns the rank of a number within a set of numbers.
You can use this function to rank a set of race times, where the lowest (fastest) time is ranked #1. In your case, you can use this function to rank athletes based on their best times and exclude their slower times.
Here’s an example formula that you can use:
=RANK(E2,$E$2:$E$10)+COUNTIF($E$2:E2,E2)-1
In this formula, E2 is the cell that contains the time for the first athlete. $E$2:$E$10 is the range of cells that contains all times for all athletes.
The COUNTIF function counts how many times a particular time appears in the range $E$2:E2.
The -1 at the end of the formula is used to adjust for ties.
You can drag this formula down to rank all athletes based on their best times.
I hope this helps!
thanks for the reply but this did not do what I'm looking for. It simply ranked each time (in reverse) including all times for athletes. Wouldn't I need to include the "athlete name" column in the formula somewhere so it knows to exclude the duplicate entries.
