Find largest n numbers

This tutorial shows how to find the n largest numbers from a specific range through the use of an Excel formula, with the IF and RANK functions

EXCEL FORMULA 1. Find largest n numbers

EXCEL

Hard coded formula
Find largest n numbers
Cell reference formula
Find largest n numbers
=IF(RANK(C5,$C$5:$C$9)<=3,"Top 3 numbers","")
=IF(RANK(C8,$C$8:$C$12)<=$C$4,$C$5,"")
GENERIC FORMULA

=IF(RANK(number,range)<=largest_n_numbers,value_if_true,value_if_false)

ARGUMENTS
number: The number to test if is one of the largest numbers.
range: A range that contains the numbers to test.
largest_n_numbers: The largest n numbers to find.
value_if_true: Value to be returned if the number is one of the n largest numbers from the selected range.
value_if_false: Value to be returned if the number is not one of the n largest numbers from the selected range.

GENERIC FORMULA

=IF(RANK(number,range)<=largest_n_numbers,value_if_true,value_if_false)

ARGUMENTS
number: The number to test if is one of the largest numbers.
range: A range that contains the numbers to test.
largest_n_numbers: The largest n numbers to find.
value_if_true: Value to be returned if the number is one of the n largest numbers from the selected range.
value_if_false: Value to be returned if the number is not one of the n largest numbers from the selected range.

EXPLANATION

This formula uses the IF and RANK functions to find the first n largest numbers from a selected range and then returns a value that has been assigned as the true value, alternatively if the number is not one of the n largest numbers from the range the formula will return a value assigned as the false value.
Click on either the Hard Coded or Cell Reference button to view the formula that has the number that represents the n largest numbers and return values directly entered into the formula or referenced to specific cells that capture these values, respectively.

In this example the formula tests each of the numbers in range C5 to C9 (hard coded example) or C8 to C12 (cell reference example) to assess which of these numbers are the largest 3 numbers and return "Top 3 numbers" (hard coded example) or value in cell C5 (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
How to lookup the nth largest value in a range through the use of an Excel formula or VBA
How to lookup the nth largest value in a range with criteria through the use of an Excel formula or VBA

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