Rank top n numbers without ties

This tutorial shows how to rank the top n numbers only, without ties, from a list through the use of an Excel formula, with the IF, RANK and COUNTIF functions

EXCEL FORMULA 1. Rank top n numbers without ties

EXCEL

Hard coded formula
Rank top n numbers without ties
Cell reference formula
Rank top n numbers without ties
=IF(RANK(C5,$C$5:$C$11,0)+COUNTIF($C$5:C5,C5)-1<=3,RANK(C5,$C$5:$C$11,0)+COUNTIF($C$5:C5,C5)-1,"")
=IF(RANK(C8,$C$8:$C$14,0)+COUNTIF($C$8:C8,C8)-1<=$C$5,RANK(C8,$C$8:$C$14,0)+COUNTIF($C$8:C8,C8)-1,"")
GENERIC FORMULA

=IF(RANK(number,range,0)+COUNTIF(dynamic_range,number)-1<=top_n,RANK(number,range,0)+COUNTIF(dynamic_range,number)-1,"")

ARGUMENTS
number: The number to test and rank if it is one of the top n numbers from a list.
range: A range that contains the numbers to rank.
dynamic_range: A dynamic range that contains the numbers to rank.
top_n: The top n numbers to rank from a list.

GENERIC FORMULA

=IF(RANK(number,range,0)+COUNTIF(dynamic_range,number)-1<=top_n,RANK(number,range,0)+COUNTIF(dynamic_range,number)-1,"")

ARGUMENTS
number: The number to test and rank if it is one of the top n numbers from a list.
range: A range that contains the numbers to rank.
dynamic_range: A dynamic range that contains the numbers to rank.
top_n: The top n numbers to rank from a list.

EXPLANATION

This formula uses the IF, RANK and COUNTIF functions to rank only the largest n numbers from a selected range, without any duplicates, whilst returning a blank cell for all other numbers in the range.
Click on either the Hard Coded or Cell Reference button to view the formula that has the top n numbers that are to be ranked directly entered into the formula or referenced to a specific cell.

In this example the formula ranks only the top 3 numbers in range C5 to C11 (hard coded example) or C8 to C14 (cell reference example). For all the other numbers the formula will return a blank cell through the use of double quotation marks (""). The formula also ranks without ties, meaning if there are two or more of the same numbers in the list the formula will provide them with sequential ranking numbers instead of the same rank.

RELATED TOPICS

Related Topic Description Related Topic and Description
Rank list without ties How to rank a range of numbers without ties
Rank top n numbers How to rank the top n numbers only from a list
Rank bottom n numbers How to rank the bottom n numbers only from a list

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria