Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

ranking athletes based on best time

Copper Contributor

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

@Sethlemley 

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!

@NikolinoDE 

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.