Home > Formulas > Statistical > RANK Excel RANK Function ExamplesUse the RANK function to compare numbers to other numbers in the same list. Watch the videos, and get the free RANK workbook |
Use the RANK worksheet function to compare numbers to other numbers in the same list. See the steps in this video, and the written instructions are below the video.
If you give the RANK function a number, and a list of numbers, it will tell you the rank of a number in the list, either in ascending or descending order.
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.
There are 3 arguments for the RANK function syntax:
In the RANK function, the 3rd argument (order), is optional. The order argument tells Excel whether to rank the list in ascending or descending order.
If you use a zero as the setting for order, or if you don't use the 3rd argument, the rank is set in descending order.
If you use a 1 as the setting for order, or if you enter any number except zero as the 3rd argument, the rank is set in ascending order.
Instead of typing the order argument number into a RANK formula, you can use a cell reference, to create a flexible formula.
For example, in the screenshot below, I typed a 1 in cell E1, and linked to cell E1 in the formula, for the order argument.
NOTE: Be sure to use an absolute reference ($E$1), if the formula will be copied down to other rows. If you use a relative reference (E1), the reference will change in each row.
=RANK(B2,$B$2:$B$6,$E$1)
By linking to a cell, you can quickly see different results, without changing the formula. Type a zero in cell E1, or delete the number, and the rank will change to Descending order.
For the order option, there are only 2 choices - Ascending or Descending. To make it easier for people to change the order, use a check box to turning Ascending order ON or OFF.
In the RANK function sample file, there is a check box example on the RankOrderCheck worksheet. For instructions on how to add a check box, and use it in a formula, see my blog post, Use Check Box Result in Excel Formula.
For more examples of flexible formulas, see
What happens to the ranking if some of the scores are tied? If there are duplicate rankings, Excel will skip subsequent numbers, if necessary, to show the correct rank.
If you have to break the ties, or award prize money based on ties, there are examples below, that show how to address those requirements.
In some cases, ties aren't allowed, so you have to find a way to break the tie.
Watch this video to see the steps, and the written instructions are below the video.
In some cases, ties aren't allowed, so you have to find a way to break the tie.
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.
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)
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.
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.
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. To see the steps for splitting the winnings, watch this short video. The written instructions are below the video.
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.
Below 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.
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)))
The Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.
Instead of using the RANK function to compare a number to an entire list of numbers, you might need to rank a value within a specific subset of numbers. For example, rank each day's sales compared to other days in the same week.
In the screen shot below, there are sales records for two weeks.
There isn't a RANKIF function, but you can use the COUNTIFS function to calculate the rank based on items with the same week number.
Enter this formula in cell D2, and copy it down to the last row with data:
=COUNTIFS([Wk], [@Wk], [Sales], ">"&[@Sales])+1
The first criterion in the formula checks for other sales with the same week number:
=COUNTIFS([Wk], [@Wk]
The second criterion find items with a larger amount in the Sales column.
[Sales],">"&[@Sales])
Then, 1 is added to that number, to get the ranking.
+1
For example, in week 1, look at the sales for Jan 3rd -- 237.
Get the zipped sample Excel RANK Function file. The file is in xlsx format, and does not contain macros.
Last updated: July 19, 2023 2:45 PM