Use the RANK function to compare numbers with other numbers in the same list. Watch the videos and get your free RANK workbook
How to use the RANGE function
RANGE function arguments
The order of the RANK function
RANK function with links
Breaking ties with the RANK function
Split Earnings for Tied Rank
RANG SI formula
Get the sample file
How to use the RANGE function
Use the RANK worksheet function to compare numbers with other numbers in the same list. Please watch the steps in this video and the written instructions are below the video.
How to use the RANGE function
If you give the RANGE function a number and a list of numbers, it will tell you the rank of the number in the list, either in ascending or descending order.
For example, in the screenshot below, there is a list of 10 student test scores, in cells B2:B11.
To find the range of scores for the first student in cell B2, enter this formula in cell C2:
=RANGO(B2,$B$2:$B$11)
Then copy the formula from cell C2 to cell C11 and the results will be sorted in descending order.
RANK function syntax arguments
There are 3 arguments to the RANK function syntax:
- number: In the example above, the rank number is in the cellB2
- referee: We want to compare a number with a list of numbers in cells$B$2:$B$11. Use an absolute reference ($B$2:$B11) instead of a relative reference (B2:B11) so that the reference range stays the same when you copy the formula into the cells below
- order: (optional) This third argument tells Microsoft Excel whether to sort the list in ascending or descending order.
- Use zero or leave this argument empty to find the rank of the numeric value in the list in descending order. In the example above, the order argument is left blank to find the rank in descending order.
=RANGO(B2,$B$2:$B$11) - For ascending order, enter 1 or any non-zero number.
If you were comparing golf scores, you could enter 1 to rank in ascending order.
=RANGO(B2,$B$2:$B$11,1)
- Use zero or leave this argument empty to find the rank of the numeric value in the list in descending order. In the example above, the order argument is left blank to find the rank in descending order.
The order of the RANK function
In the RANK function, the third argument (order), it's optional. The order argument tells Excel whether to sort the list in ascending or descending order.
Descending order
If you use zero as the order setting or if you don't use the third argument, the range is set in descending order.
- Helargernumber gets rank 1
- The fifth highest number gets rank 5.
ascending order
If you use 1 as the order setting or if you enterany number except zeroas the third argument, the range is set in ascending order.
- Heleastnumber gets rank 1
- The fifth smallest number gets rank 5.
flexible formula
Instead of writing the rank argument number in the RANK formula, use a cell reference to create a flexible formula.
For example, type 1 in cell E1 and link to cell E1 for the row argument.
NOTE: Be sure to use an absolute reference ($E $1), whether the formula will be copied to other lines. If you use a relative reference (E1), the reference will change on each line.
=RANGO(B2,$B$2:$B$6,$E$1)
By linking to a cell, you can quickly see different results, without changing the formula. Type zero in cell E1 or delete the number and the sorting will change to descending.
Use the check box
There are only 2 options for the order option: ascending or descending. To make it easier for people to change the order, use the checkbox to turn ascending order on or off.
- If on, the RANK order will be ascending
- If disabled, the RANK order will be descending
There is an example of a checkbox in the RankOrderCheck worksheet in the RANK function sample file. For instructions on how to add a checkbox and use it in a formula, see my blog post,Use checkbox result in an excel formula.
More examples of flexible formulas
For more examples of flexible formulas, see
- how to use dropdown menus toCONVERT functionunits
- how to choose optionsfor SUBTOTALiadded functions.
RANGE function bindings
What happens to the ranking if some of the scores are tied? If there are duplicate ranges, Excel will skip the next numbers, if necessary, to display the correct range.
- In this example above, the last two results in the list are equal: 38. Two students, Ivy and Joe, are bothranked 4to.
- The next highest score, Ed's score of 36, isranked 6th, not 5. because there are 5 students in front of him.
If you must break a tie or award prize money based on a tie, below are examples showing how to meet these requirements.
Breaking ties with the RANK function
Split Earnings for Tied Rank
Breaking ties with the RANK function
In some cases, ties are not allowed, so you have to find a way to break the tie.
Please watch this video to see the steps and instructions written below the video.
Breaking ties with the RANK function
In some cases, ties are not allowed, so you have to find a way to break the tie.
In this example, you can track the number of minutes each student worked on the test, and use that time to break all ties. If the scores are tied, the student who takes the shortest time to complete the test will be ranked ahead of another student with the same score.
Calculate the decimal amount for tied scores
I added the test times in column C and the TieBreak formula in column E.
=IF(COUNTIF($B$2:$B$11,B2)>1,
RANGO(C2,$C$2:$C$11,1)/100,0)
How the Tie Break Formula Works
The tie formula uses the COUNTIF and RANK functions, wrapped with an IF function, to see if the decimal amount of the tie should be added to the original range.
- First, the TieBreak formula checks if there is more than one instance of a number in the entire list:
SI (CONTAR.SI ($ B $ 2: $ B $ 11, B2)> 1 - If there is more than one instance, then the times are placed in orderrisingorder, because less time is better:
RANGO(C2,$C$2:$C$11,1) - Then divide that amount by 100 to get the decimal amount. Later you will add this decimal amount to the original range.
Use: The divisor, 100, can 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 of Rank, the result of TieBreak is zero.
,0)
Calculate the final classification
After calculating the linked decimal amounts, you can add the results of the RANK function to the TieBreak results to get the final rank.
In this example, two students were tied for fourth place. It took Joe 27 minutes to complete the test and his time ranked fifth. It took Ivy 29 minutes to complete the test and her time placed ninth.
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 at 4.05 is better than Ivy at 4.09.
Split Earnings for Tied Rank
In a tournament, instead of breaking ties, you may want to split the winnings among the tied players if you award cash or point prizes. To see the steps to split the winnings, watch this short video. There are instructions written below the video.
Split Earnings for Tied Rank
In a tournament, instead of breaking ties, you may want to split the winnings among the tied players if you award cash or point prizes. If 2 or more players have the same rank, they share the prize amount available for that rank, up to the next rank occupied.
Below is an example of a reward table, showing the amount awarded for each rank. In this example, if 3 players are rank 1, they would split the total amount (10+9+8=27) for ranks 1, 2, and 3.
The 3 players in rank 1 each win 9 (27/3 = 9) and the player with the next highest score would be ranked fourth and win 7.
Calculate the amount divided
To distribute the prize amount among the tied players, the prize formula uses the AVERAGE function, and the OFFSET function finds the range of cells for the average. This formula is entered in cell D2 and copied to cell D11.
=AVERAGE(OFFSET($K$1,C2,0, COUNTIF($C$2:$C$11,C2)))
How the prize formula works
The Award formula uses the AVERAGE function and the OFFSET function finds the range of cells for the average.
- The AVERAGE function will calculate the amount for each player based on a specified range of cells:
AVERAGE ( - The OFFSET function returns a range with the amounts used to average:
OFFSET( - In the OFFSET formula, the first argument is the reference cell. In this example, that's cell K1, the header of the Prize Amounts column.
2 k$, - In the OFFSET formula, the second argument is the number of rows down from the reference cell, where the average cells start from. The ranges are listed in ascending order, so for a range of 1, the cells to average would start 1 row below the $K$1 reference cell. The rank of the first player is in cell C2, so look it up in the formula
C2, - In the OFFSET formula, the third argument is the number of columns to the right of the reference cell, from where the average cells start. You want to find the quantities in the same column, so the number is zero
0, - In the OFFSET formula, the fourth argument is the number of rows to include in the range. This should be the number of players tied for that rank. The COUNTIF function will count the instances of the range in column C, which are equal to the range in C2
CONTAR.SI ($ C $ 2: $ C $ 11, C2)
RANG SI formula
Instead of using the SORT function to compare a number against an entire list of numbers, you may need to rank a value within a specific subset of numbers. For example, rank the sales for each day compared to other days in the same week.
The following screenshot shows the sales records for two weeks.
- January 2 and 4 have the highest sales in week 1, so they should be ranked #1.
- In the second week of January 10 it has the highest sales, so it should be ranked 1 for that week.
No RANKIF function
There is no 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 to the last row of data:
=COUNTIF([Week], [@Week], [Sales], ">"&[@Sales])+1
How it works
The first criterion in the formula checks for other sales with the same week number:
=COUNTIF([Week], [@Week]
The second criteria looks for items with a higher amount in the Sales column.
[Sale],">"&[@Sale])
Then 1 is added to that number to get the order.
+1
For example, in week 1, look at the sales for January 3: 237.
- There are 2 sale dates in week 1: January 2 and 4.
- Add 1 to that number and January 3 is in rank 3
Get the sample file
Take a compressed sampleRANGE Excel functionarchive. The file is in xlsx format and does not contain macros.
related links
AVERAGE
COUNT / CONTAR.SI
BUSCARH
MIN MAX
SUM / SUM.IF
VLOOKUP
Formulas, the beginning
FAQs
How do you use rank formula in Excel with example? ›
RANK gives duplicate numbers the same rank. However, the presence of duplicate numbers affects the ranks of subsequent numbers. For example, in a list of integers sorted in ascending order, if the number 10 appears twice and has a rank of 5, then 11 would have a rank of 7 (no number would have a rank of 6).
How do you use the rank function in Excel? ›=RANK(number,ref,[order])
The RANK function uses the following arguments: Number (required argument) – This is the value for which we need to find the rank. Ref (required argument) – Can be a list of, or an array of, or reference to, numbers.
- =SORT(array, [sort_index], [sort_order], [by_col])
- SORT(A2:B20,2,-1): Means to sort the cell range A2:A20 on the second column in descending order.
- =SEQUENCE(rows, [columns], [start], [step])
Tied observations are given the average of the ranks they would have received as if no ranks were tied. For example, if three teams are tied for first, they are also statistically tied for second and for third as well.
How do you calculate rank? ›- Find the percentile of your data set. ...
- Find the number of items in the data set. ...
- Multiply the sum of the number of items and one by 100. ...
- Divide the percentile by the product of 100 and n+1.
Type a comma and then select the column of data through which we will search, in this case, the Rank column. Type a comma and then type 0 (zero) to make sure the lookup only returns an exact match. Then put two closing parentheses after the zero. Hit enter and that's it!
How do I use dynamic numbering in Excel? ›- Select the first cell in the range that you want to fill.
- Type the starting value for the series.
- Type a value in the next cell to establish a pattern. ...
- Select the cells that contain the starting values. ...
- Drag the fill handle.
Select cell B2, copy and paste formula =RANK(A2,$A$2:$A$11,1)+COUNTIF($A$2:A2,A2)-1 into the Formula Bar, then press the Enter key. Then the first ranking number is displayed in cell B2. 2. Select the cell B2, drag the fill handle down to the cell B11, then the unique ranking is finished.
What is the formula for ranking in reasoning? ›The general formula and basic for the ranking position is given as Total = [ Left + Right ] -1 However when overlapping happens then use [ Left side rank+Right side rank ] – T, where (Left side rank+Right side rank )> T.
What is dense rank in Excel? ›One is the DENSE_RANK function, which calculates the rank of a value within a range of values, but gives equal rank to ties. Another is the RANK. EQ function, which calculates the rank of a value within a range of values, and gives equal rank to ties.
What is an example of a tied rank? ›
In case of ties, the tied observations receive the same average rank. For example, if three observations of X are tied for the third smallest value, the ranks involved are 3, 4, and 5. The average of these three ranks is 4, and that is the rank that each of the three observations would be assigned.
What is the rule of rank? ›What does Rule of rank mean? The principle of interpretation of statutes and other documents that where a list of things is followed by general words, the general words to not apply to things to a different rank.
How does a rank work? ›A ranking is a relationship between a set of items such that, for any two items, the first is either "ranked higher than", "ranked lower than" or "ranked equal to" the second. In mathematics, this is known as a weak order or total preorder of objects.
Can you automatically rank in Excel? ›The Excel RANK function assigns a rank to a numeric value when compared to a list of other numeric values. Use RANK when you want to display a rank for numeric values in a list.
What is the formula for rank by group in Excel? ›There is a formula to quickly rank values based on group. Select a blank cell next to the data, C2 for instance, type this formula, =SUMPRODUCT(($A$2:$A$11=A2)*(B2<$B$2:$B$11))+1 then drag autofill handle down to apply this formula to the cells you need.
How do I arrange rank from highest to lowest in Excel? ›- Select a single cell in the column you want to sort.
- Click Sort A to Z to perform an ascending sort (A to Z or smallest number to largest).
- Click Sort Z to A to perform a descending sort (Z to A or largest number to smallest).
1) Write =ROW(A1) in your first cell, 2) It will appear as the number 1 , 3) Click and drag or double-click to fill all other cells. 4) Now if you sort the data, the line numbers will stay in order.
What is dynamic numbering? ›Dynamic Number Insertion (DNI) is a call tracking feature where a unique phone number is tied to each ad source. This helps marketers analyze offline behavior much in the same way they track online behavior with the help of cookies.
Which is better rank EQ or rank AVG in Excel? ›The difference between these two functions occurs when there are duplicates in the list of values. The Rank. Eq function returns the lower rank, whereas the Rank. Avg function returns the average rank.
How do you decile rank in Excel? ›Enter the following formula in cell B1: =PERCENTRANK($A$1:$A$12000,A1,1), assuming your numbers are in cells A1 through A12000. The percent rank of the value in cell A1, reduced down to one decimal place, is calculated using the set of values in cells $A$1:$A$12000 (which is all you need to identify the decile).
What does rank average do in Excel? ›
Description. Returns the rank of a number in a list of numbers: its size relative to other values in the list; if more than one value has the same rank, the average rank is returned.
How do you break ties with rank function? ›To break ties we will add an extra number to the rank based on whether there are more than one sitting in that same position. To do this we will use the COUNTIFS function. Excel's Description: The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
Why rank skips the sequence? ›If there are duplicates in your data resulting in 2-way or 3-way ties, each of those in the tied group will receive the same rank value. However, the list will then skip the number of following rank positions relative to the number of items in the prior tied group.
How do you solve rank questions? ›Find the total number of persons in the row. To find the position of a person from the opposite side, when rank from one side and total no. of persons are given. Example: In a row of 42 persons, Position of Ramesh from the right side of the row is 22nd.
What is ranking logic? ›A ranking algorithm is a procedure that ranks items in a dataset according to some criterion. Ranking algorithms are used in many different applications, such as web search, recommender systems, and machine learning.
What is the formula of rank? ›| Data | ||
|---|---|---|
| =RANK.EQ(A2,A2:A6,1) | Rank of 7 in the list contained in the range A2:A6. Because the Order argument (1) is a non-zero value, the list is sorted lowest to highest. | 5 |
| =RANK.EQ(A6,A2:A6) | Rank of 2 in the same list. Because the Order argument is omitted, the list is sorted, by default, highest to lowest. | 4 |
CHOOSE() for Ordinal numbers
The non-macro way is to use CHOOSE() which uses the first parameter (the ranking) and returns the parameter in the second position for 1, third position for 2 etc.
How to Use the RANK formula in Google Sheets. Type “=RANK(” or go to “Insert” → “Function” → “Statistical” → “RANK”. Select a value whose rank is determined. Input a range containing all numbers you want to rank.
What is rank of function? ›The RANK function is an OLAP ranking function that calculates a ranking value for each row in an OLAP window. The return value is an ordinal number, which is based on the required ORDER BY expression in the OVER clause.