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
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 GENERIC FORMULA
=IF(RANK(number,range,1)<=top_n,RANK(number,range,1),"")
ARGUMENTS 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 TOPICS
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 |
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 |