Rank bottom n numbers
This tutorial shows how to rank the bottom 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,1)<=3,RANK(C5,$C$5:$C$11,1),"")
=IF(RANK(C8,$C$8:$C$14,1)<=$C$5,RANK(C8,$C$8:$C$14,1),"")
|
GENERIC FORMULA
=IF(RANK(number,range,1)<=bottom_n,RANK(number,range,1),"")
ARGUMENTS GENERIC FORMULA
=IF(RANK(number,range,1)<=bottom_n,RANK(number,range,1),"")
ARGUMENTS EXPLANATION This formula uses the IF and RANK functions to rank only the smallest 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 bottom 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 smallest 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 |
|---|---|---|
| Find smallest n numbers | How to find smallest n numbers | |
| Rank top n numbers | How to rank the top 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 |