Learn how to rank and compare a list of values in Microsoft Excel using the RANK() function.
Excel's RANK() function returns the rank of a value within the context of a list of values. By rank, I mean a value's relative position to the other values in the list. You could just sort the list, but that's not always practical and doing so won't return a rank, although you can easily see which values rank highest and lowest in a sorted list.
The figure below shows the RANK() function at work in a simple spreadsheet. The function in cells F2:F5 returns the rank of the four values in E2:E5. Those values are the result of the following SUMIF() function:
The SUMIF() returns a total for each individual listed in column A. (You can recreate this spreadsheet or work with a simple column of values.)
The RANK() function has three arguments:
where number is the value you're ranking, reference identifies the list of values you're comparing number against, and order specifies an ascending or descending rank. If you omit order, Excel assumes the value 0, which ranks values in descending order. Any value other than 0 ranks in ascending order. In this example, I enter the following function into cell F2:
Notice that number is relative but reference is absolute. You'll want to maintain that structure when applying this to your own spreadsheet. Copy the function in F2 to F3:F5. The largest value, 120, returns a rank of 1. The lowest value, 98, is 4. To reverse the ranking order, include order as follows:
Understanding a tie
Something you'll want to watch for is a tie. RANK() will return the same rank for a value that occurs more than once. Interestingly, RANK() accommodates the tie by skipping a rank value. For instance, the following spreadsheet shows what happens when both Alexis and Kate have the same value (101). The rank for both is 2 and there's no rank of 3. The lowest value still ranks as 4.
There's no argument to change this behavior. If a tie isn't valid, you must find a second set of criteria to include in the comparison.
Stay on top of the latest Microsoft Windows tips and tricks with TechRepublic's Windows Desktop newsletter, delivered every Monday and Thursday. Automatically sign up today!