Rank if list contains more than or equal to n entries

This tutorial shows how to rank a set of numbers if a list contains more than or equal to n entries through the use of an Excel formula, with the IF, COUNT and RANK functions

EXCEL FORMULA 1. Rank if list contains more than or equal to n entries

EXCEL

Hard coded formula
Rank if list contains more than or equal to n entries
Cell reference formula
Rank if list contains more than or equal to n entries
=IF(COUNT($B$5:$B$9)>=4,RANK(B5,$B$5:$B$9,0),"Not enough data")
=IF(COUNT($B$8:$B$12)>=$C$5,RANK(B8,$B$8:$B$12,0),"Not enough data")
GENERIC FORMULA

=IF(COUNT(range)>=entries,RANK(number,range,0),value_if_false)

ARGUMENTS
number: The number to rank.
range: A range that contains the numbers you want to rank.
entries: A number that represents n entries that you want to test the range against.
value_if_false: Value to be returned if the range contains less than n entries.

GENERIC FORMULA

=IF(COUNT(range)>=entries,RANK(number,range,0),value_if_false)

ARGUMENTS
number: The number to rank.
range: A range that contains the numbers you want to rank.
entries: A number that represents n entries that you want to test the range against.
value_if_false: Value to be returned if the range contains less than n entries.

EXPLANATION

This formula ranks a set of numbers if the list containing these numbers has more than or equal to 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 equal to or 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 4 then the formula would return a text value of "Not enough data".

RELATED TOPICS

Related Topic Description Related Topic and Description
Rank if list contains more than n entries How to rank a set of numbers if a list contains more than n entries
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

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