Imagine that we're the announcer at a high school track meet, and the runners are currently competing in the 1500m race.

Our job is to take their times, and then rank them from lowest to highest.

To do this, we can use the RANK function on each runner's time!

The RANK function enables us to easily see where a value ranks within a list.

Coding a RANK

Given the above track meet example, let's say that each runner scores the following times:

Let's start by ranking the first runner. To do so, we'll use the following template:

=RANK(value, range, [order])

In our case, the first value will be cell B2, since that's where Michael's time is stored.

=RANK(B2, range, [order])

Next, for the range. This represents the cells contain all the values to be ranked. In this case, that's cells B2:B6.

=RANK(B2, B2:B6, [order])

We can leave [order] blank for now because...

Arguments in [ ] are not required.

When we plug this RANK function into our spreadsheet, we get the following result:

Somethings wrong here... why did Michael get ranked 4th but had the 2nd best time?

The reason is because our RANK function (by default) ranks in descending order (meaning the highest values get the top rankings). Since we're dealing with a race, we want the lowest values to get the top rankings, so we must specify we're ranking in ascending order.

To do so, we need to place a "1" in the [order] argument of our RANK function.

=RANK(B2, B2:B6, 1)

The [order] argument enables us to rank in descending order (value of 0, is default) or ascending order (value of 1).

Notice now that Michael is properly ranked in 2nd place!

Now, what about the rest of the runners? To get their ranks, we must first specify an absolute cell reference for our range.

=RANK(B2, $B$2:$B$6, 1)

After doing so, all we need to do is copy the formula for Michael's ranking down to all the runners like so!