Software

How to use Microsoft Excel's RANK() function

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:

=SUMIF($A$2:$A$9,$D2,$B$2:$B$9)

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.)

About RANK()

The RANK() function has three arguments:

RANK(number,reference,[order])

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:

=RANK(E2,$E$2:$E$5)

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:

=RANK(E2,$E$2:$E$5,1) 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!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

7 comments
lee_dimambro
lee_dimambro

The SMALL and Large functions can be used to create a customized Rank function. If there is a way to attach a speadsheet let me know and I will send the one that I created. Create a column of numbers Row | Column A 2 | 40 3 | 2 4 | 56 5 | 71 6 | 23 7 | 40 8 | 57 9 | 2 Copy this formula in B2:B9 it will create a sorted list. =+SMALL($A$2:$A$9,ROW(1:1)) Sorted Ascending Using SMALL Function 2 2 23 40 40 56 57 71 Copy this formula in C2:C9 it will create a dense rank list it will not skip any numbers. =IF(SMALL($A$2:$A$9,ROW(1:1))>0,1,0) Dense Rank 1 1 2 3 3 4 5 6 This formula will create a list in decending order. =+LARGE($A$2:$A$9,ROW(1:1)) Sorted Decending Using LARGE Function 71 57 56 40 40 23 2 2

Marshwiggle
Marshwiggle

In the example provided, you can handle ONE tie by adding (and copying down)the following to Col G: =IF(COUNTIF($F$2:$F$5,F2-2)>1,F2-1,F2), and hiding Col F. I suspect that you could handle a larger sample, w/ more than one tie, w/ a really long OR() function based on the number of values in Col F, e.g.: OR(COUNTIF($F$13:$F$24,F13-3)>1,COUNTIF($F$13:$F$24,F13-2)>1,etc.) ... but I'll leave that to someone w/ more ambition.

emailforsid
emailforsid

I can wholeheartedly agree with stapleb ~ the function has (to me) a counter-intuitive aspect . . . but then it is perhaps more likey that I am the one that is "odd". Here is my question. What is / Where is the comparative feature in Access please? I have Office 2003 in mind ~ where an internet search sends you off down the road of "embedded secondary queries (!)" or some such thing. Is there a "RANK" equivalent in 2007 / 2010? SC

stapleb
stapleb

I have shown people how to use this, and my poor brain had a hard time working out descending and ascending sort for the rank value. "It" finally came to the party and worked out how the results were being presented - how sad is that. You are right Susan that repeat values do "mess" with the results, and I can't think of a way to get around it. Let's hope one of the gurus can come up with an answer.

Marshwiggle
Marshwiggle

... but haven't had an opportunity to work through it yet. Looks good, but what is meant by "dense rank"?

lee_dimambro
lee_dimambro

Dense Rank means that no numbers are skipped. For example These are the numbers that are to be ranked Sorted Ascending 2 2 23 40 40 56 57 71 This is how the Rank function would rank them notice that the number 2 and 5 are missing because of the ties for 2 and 40 in the sample above. Sparse Rank 1 1 3 4 4 6 7 8 In the Dense Rank the first are ranked as 1 however the next number is ranked as 2. In other words the Dense Rank does not skip numbers in the ranking. Dense Rank 1 1 2 3 3 4 5 6