Rank formula example #2: RANK and Time
You can also use the RANK function to rank times (highest to lowest or vice versa).
The image below shows a set of times.
Let’s rank them all to see the relative position of each time in the list.
1. Write the RANK function as below.
= RANK (A2, $A$2:$A$6)
We have created a reference for the first time in the list.
The remaining times (the whole list) are referenced as the range. Must note that this is an absolute reference.
2. Hit ‘Enter’ to see the result.
Drag and Drop the results to rank all times.
3. Excel ranks the latest time 7:45 PM as ‘1’ and so on.
4. If you want Excel to rank it otherwise i.e. earliest time first, add a third argument ‘1’ to the above formula.
= RANK (A2, $A$2:$A$6, 1)
Drag and Drop the same to the whole list.
See the change from the previous example? Excel ranks the earliest time 12:00 AM as ‘1’ and so on.
That’s how you can rank any numeric value in Excel and find its position relative to a list of numbers in no time.