Excel RANK Function Examples

Search Contextures Sites

Excel RANK Function Examples

RANK Function Basics
RANK Function Arguments
RANK Function With Ties
Break Ties With RANK Function
Split Winnings for Tied Rank
Watch the Excel Video Tutorial

Download the zipped sample Excel RANK Function file

RANK Function Basics

If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order.

To view the Excel RANK function steps in a short video, click here Excel Rank Function Basics Video

For example, in the screen shot below, there is a list of 10 student test scores, in cells B2:B11.

To find the rank of the the first student's score in cell B2, enter this formula in cell C2:

=RANK(B2,$B$2:$B$11)

Then, copy the formula from cell C2 down to cell C11, and the scores will be ranked in descending order.

RANK Function Arguments

There are 3 arguments for the RANK function:

number: in the above example, the number to rank is in cell B2

ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the referenced range will stay the same when you copy the formula down to the cells below

order: (optional) This argument tells Excel whether to rank the list in ascending or descending order.

Use zero, or leave this argument empty, to find the rank in the list in descending order. In the example above, the order argument was left blank, to find the rank in descending order.
=RANK(B2,$B$2:$B$11)

For ascending order, type a 1, or any other number except zero.
If you were comparing golf scores, you could type a 1, to rank in ascending order.
=RANK(B2,$B$2:$B$11,1)

RANK Function With Ties

What happens to the ranking if some of the scores are tied?

In our example, the last two scores in the list are the same -- 38. The two students, Ivy and Joe, are both ranked as 4.

The next highest score -- Ed's score of 36 -- is ranked as 6th, not 5th, because there are 5 students ahead of him.

.

Break Ties With RANK Function

In some cases, ties aren’t allowed, so you have to find a way to break the tie.

To view the Break Ties with Excel RANK function steps in a short video, click here Excel Rank Function Break Ties Video

In this example, you could keep track of the number of minutes that each student worked on the test, and use that time to break any ties. If scores are tied, the student who takes less time to complete the test will rank ahead of the other student with the same score.

Calculate Decimal Amount for Tied Scores

I added the Test Times in column C, and a TieBreak formula in column E.

=IF(COUNTIF($B$2:$B$11,B2)>1,
       RANK(C2,$C$2:$C$11,1)/100,0)

How the Tie Break Formula Works

The Tie Break formula uses COUNTIF and RANK functions, wrapped with an IF function, to see if a tie breaking decimal amount should be added to the original Rank.

First, the TieBreak formula checks to see IF there is more than one instance of the number in the entire list:
   IF(COUNTIF($B$2:$B$11,B2)>1

If there is more than one instance, it ranks the Times in ascending order, because a lower time is better:
   RANK(C2,$C$2:$C$11,1)

Next, it divides that amount by 100, to get a decimal amount. Later, you'll add this decimal amount to the original Rank.
Note: The divisor, 100, could be changed to another number, if you are working with a longer list.
   /100

Finally, to complete the IF function, if there is only one instance a a Rank, the result for the TieBreak is zero.
   ,0)

Calculate the Final Rank

After calculating the tie breaking decimal amounts, you can add the RANK function results to the TieBreak results, to get the final ranking.

In this example, two students were tied in 4th place. Joe took 27 minutes to complete the test, and his Time was ranked 5th. Ivy took 29 minutes to complete the test, and her Time was ranked 9th.

The Tie Break formula adds a decimal of 0.09 to Ivy's score, and 0.05 to Joe's score. In the final ranking, Joe, with 4.05 ranks higher than Ivy, with 4.09.

Split Winnings for Tied Rank

In a tournament, instead of breaking the ties, you might want to split the winnings among any tied players, if you're awarding a cash prize, or points. If 2 or more players have the same rank, they split the prize amount available for that rank, down to the next occupied rank.

To view the Excel RANK Split Winnings steps in a short video, click here Excel Rank Function Split Winnings Video

At the right is a sample prize table, showing the amount awarded for each rank. In this example, if 3 players are at rank 1, they would split the total amount (10+9+8=27) for ranks 1, 2 and 3.

Each of the 3 players at rank 1 earns 9 (27/3 = 9) and the player with the next highest score would be ranked 4th, and earn 7.

Calculate the Split Amount

To split the prize amount among tied players, the Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.This formula is entered in cell D2 and copied down to cell D11.

=AVERAGE(OFFSET($K$1,C2,0, COUNTIF($C$2:$C$11,C2)))

How the Prize Formula Works

The Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.

The AVERAGE function will calculate the amount for each player, based on a specific range of cells:
   AVERAGE(

The OFFSET function returns the range with the amounts to use for the average:
   OFFSET(

In the OFFSET formula, the 1st argument is the reference cell. In this example, that is cell K1 -- the heading for the Prize amounts column.
   $K$2,

In the OFFSET formula, the 2nd argument is the number of rows down from the reference cell, that the cells to average start. The ranks are listed in ascending order, so for the Rank of 1, the cells to average would start 1 row down from the Reference cell of $K$1. The first player's rank is in cell C2, so refer to that in the formula
   C2,

In the OFFSET formula, the 3rd argument is the number of columns to the right of the reference cell, that the cells to average start. You want to find amounts in the same column, so the number is zero
   0,

In the OFFSET formula, the 4th argument is the number of rows to include in the range. This should be the number of players who are tied at that rank. The COUNTIF function will count the instances of the rank in column C, that are equal to the rank in C2
   COUNTIF($C$2:$C$11,C2)

To view the Excel RANK function steps in 3 short videos, click here Excel Rank Function Videos

Download the zipped sample Excel RANK Function file

Excel Function Tutorials

Excel SUM Function
Excel VLOOKUP Function
Excel INDEX function and Excel MATCH Function
Excel Count Function
Excel INDIRECT Function

Excel Function Video Tutorials

Excel MATCH Function Video Tutorial
Excel VLOOKUP Function Video Tutorial
Hide Excel VLOOKUP Function Errors With IF and ISNA

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright ©2010
All rights reserved.

Debra Dalgleish

Last updated: September 12, 2010

	RANK Function Basics RANK Function Arguments RANK Function With Ties Break Ties With RANK Function Split Winnings for Tied Rank Watch the Excel Video Tutorial Download the zipped sample Excel RANK Function file

	RANK Function Basics If you give the RANK function a number, and a list of numbers, it will tell you the rank of that number in the list, either in ascending or descending order. To view the Excel RANK function steps in a short video, click here Excel Rank Function Basics Video For example, in the screen shot below, there is a list of 10 student test scores, in cells B2:B11. To find the rank of the the first student's score in cell B2, enter this formula in cell C2: =RANK(B2,$B$2:$B$11) Then, copy the formula from cell C2 down to cell C11, and the scores will be ranked in descending order.

	RANK Function Arguments There are 3 arguments for the RANK function: number: in the above example, the number to rank is in cell B2 ref: We want to compare the number to the list of numbers in cells $B$2:$B$11. Use an absolute reference ($B$2:$B11), instead of a relative reference (B2:B11)so the referenced range will stay the same when you copy the formula down to the cells below order: (optional) This argument tells Excel whether to rank the list in ascending or descending order. Use zero, or leave this argument empty, to find the rank in the list in descending order. In the example above, the order argument was left blank, to find the rank in descending order. =RANK(B2,$B$2:$B$11) For ascending order, type a 1, or any other number except zero. If you were comparing golf scores, you could type a 1, to rank in ascending order. =RANK(B2,$B$2:$B$11,1)

	RANK Function With Ties What happens to the ranking if some of the scores are tied? In our example, the last two scores in the list are the same -- 38. The two students, Ivy and Joe, are both ranked as 4. The next highest score -- Ed's score of 36 -- is ranked as 6th, not 5th, because there are 5 students ahead of him. .

	Break Ties With RANK Function In some cases, ties aren’t allowed, so you have to find a way to break the tie. To view the Break Ties with Excel RANK function steps in a short video, click here Excel Rank Function Break Ties Video In this example, you could keep track of the number of minutes that each student worked on the test, and use that time to break any ties. If scores are tied, the student who takes less time to complete the test will rank ahead of the other student with the same score. Calculate Decimal Amount for Tied Scores I added the Test Times in column C, and a TieBreak formula in column E. =IF(COUNTIF($B$2:$B$11,B2)>1, RANK(C2,$C$2:$C$11,1)/100,0) How the Tie Break Formula Works The Tie Break formula uses COUNTIF and RANK functions, wrapped with an IF function, to see if a tie breaking decimal amount should be added to the original Rank. First, the TieBreak formula checks to see IF there is more than one instance of the number in the entire list: IF(COUNTIF($B$2:$B$11,B2)>1 If there is more than one instance, it ranks the Times in ascending order, because a lower time is better: RANK(C2,$C$2:$C$11,1) Next, it divides that amount by 100, to get a decimal amount. Later, you'll add this decimal amount to the original Rank. Note: The divisor, 100, could be changed to another number, if you are working with a longer list. /100 Finally, to complete the IF function, if there is only one instance a a Rank, the result for the TieBreak is zero. ,0) Calculate the Final Rank After calculating the tie breaking decimal amounts, you can add the RANK function results to the TieBreak results, to get the final ranking. In this example, two students were tied in 4th place. Joe took 27 minutes to complete the test, and his Time was ranked 5th. Ivy took 29 minutes to complete the test, and her Time was ranked 9th. The Tie Break formula adds a decimal of 0.09 to Ivy's score, and 0.05 to Joe's score. In the final ranking, Joe, with 4.05 ranks higher than Ivy, with 4.09.

	Split Winnings for Tied Rank In a tournament, instead of breaking the ties, you might want to split the winnings among any tied players, if you're awarding a cash prize, or points. If 2 or more players have the same rank, they split the prize amount available for that rank, down to the next occupied rank. To view the Excel RANK Split Winnings steps in a short video, click here Excel Rank Function Split Winnings Video At the right is a sample prize table, showing the amount awarded for each rank. In this example, if 3 players are at rank 1, they would split the total amount (10+9+8=27) for ranks 1, 2 and 3. Each of the 3 players at rank 1 earns 9 (27/3 = 9) and the player with the next highest score would be ranked 4th, and earn 7. Calculate the Split Amount To split the prize amount among tied players, the Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.This formula is entered in cell D2 and copied down to cell D11. =AVERAGE(OFFSET($K$1,C2,0, COUNTIF($C$2:$C$11,C2))) How the Prize Formula Works The Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average. The AVERAGE function will calculate the amount for each player, based on a specific range of cells: AVERAGE( The OFFSET function returns the range with the amounts to use for the average: OFFSET( In the OFFSET formula, the 1st argument is the reference cell. In this example, that is cell K1 -- the heading for the Prize amounts column. $K$2, In the OFFSET formula, the 2nd argument is the number of rows down from the reference cell, that the cells to average start. The ranks are listed in ascending order, so for the Rank of 1, the cells to average would start 1 row down from the Reference cell of $K$1. The first player's rank is in cell C2, so refer to that in the formula C2, In the OFFSET formula, the 3rd argument is the number of columns to the right of the reference cell, that the cells to average start. You want to find amounts in the same column, so the number is zero 0, In the OFFSET formula, the 4th argument is the number of rows to include in the range. This should be the number of players who are tied at that rank. The COUNTIF function will count the instances of the rank in column C, that are equal to the rank in C2 COUNTIF($C$2:$C$11,C2)

	To view the Excel RANK function steps in 3 short videos, click here Excel Rank Function Videos Download the zipped sample Excel RANK Function file