Rank if list contains more than n entries
This tutorial shows how to rank a set of numbers if a list contains more than n entries through the use of an Excel formula, with the IF, COUNT and RANK functions
|
GENERIC FORMULA
=IF(COUNT(range)>entries,RANK(number,range,0),value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(COUNT(range)>entries,RANK(number,range,0),value_if_false)
ARGUMENTS EXPLANATION This formula ranks a set of numbers if the list containing these numbers has more than n amount of entries with the use of the IF, COUNT and RANK and functions.
Click on either the Hard Coded or Cell Reference button to view the formula that has the value that represents n entries directly entered into the formula or referenced to a specific cell.
In this example the formula will rank the values in range B5 to B9 (hard coded example) or B8 to B12 (cell reference example) if the amount of entries in this range is greater than 4. Given that each of the cells in this range are populated with values, meaning there is a total of five entries, this formula will rank each of the numbers in the selected range. If the number of entries in this range were less than or equal to 4 then the formula would return a text value of "Not enough data". |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Rank if greater than zero | How to rank a number if it is greater than zero (0) | |
| Rank list without ties | How to rank a range of numbers without ties |
| 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 | |
| COUNT Function | The Excel COUNT function returns the number of cells that contain numeric values in a specified range |