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

# 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

# Re: ranking athletes based on best time

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!

# Re: ranking athletes based on best time

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.