Rank if list contains less than or equal to n entries

This tutorial shows how to rank a set of numbers if a list contains less 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 less than or equal to n entries

EXCEL

Hard coded formula
Rank if list contains less than or equal to n entries
Cell reference formula
Rank if list contains less than or equal to n entries
=IF(COUNT($B$5:$B$9)<=,RANK(B5,$B$5:$B$79,0),"Too much data")
=IF(COUNT($B$8:$B$12)<=$C$5,RANK(B8,$B$8:$B$12,0),"Too much 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 more 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 more than n entries.

EXPLANATION

This formula ranks a set of numbers if the list containing these numbers has less 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 less than 7. 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 more than 7 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 list contains less than n entries How to rank a set of numbers if a list contains less than n entries
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