Rank if list contains less than n entries

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

EXCEL

Hard coded formula
Rank if list contains less than n entries
Cell reference formula
Rank if list contains less than n entries
=IF(COUNT($B$5:$B$9)<7,RANK(B5,$B$5:$B$9,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 greater than or equal to 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 greater than or equal to n entries.

EXPLANATION

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

RELATED TOPICS

Related Topic Description Related Topic and Description
How to rank a set of numbers if a list contains more than n entries
How to rank a set of numbers if a list contains more than or equal to n entries

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
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
The Excel COUNT function returns the number of cells that contain numeric values in a specified range