Rank top n numbers

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

Excel

Rank top n numbers using IF and RANK functions

EXCEL FORMULA 1. Rank top n numbers

EXCEL

Hard coded formula

Cell reference formula

=IF(RANK(C5,$C$5:$C$11,0)<=3,RANK(C5,$C$5:$C$11,0),"")

=IF(RANK(C8,$C$8:$C$14,0)<=$C$5,RANK(C8,$C$8:$C$14,0),"")

GENERIC FORMULA

=IF(RANK(number,range,1)<=top_n,RANK(number,range,1),"")

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

GENERIC FORMULA

=IF(RANK(number,range,1)<=top_n,RANK(number,range,1),"")

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

EXPLANATION

This formula uses the IF and RANK functions to rank only the largest n numbers from a selected range, 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 C7 to C13 (cell reference example). For all the other numbers the formula will return a blank cell through the use of double quotation marks ("").

Related Topic	Description	Related Topic and Description
Lookup nth largest value	How to lookup the nth largest value in a range
Find largest n numbers	How to find largest n numbers