Rank ignoring blank cells

This tutorial shows how to rank numbers whilst ignoring blank cells through the use of an Excel formula, with the IF, RANK and COUNTIF functions

EXCEL FORMULA 1. Rank ignoring blank cells

EXCEL

Rank ignoring blank cells

=IF(C5<>"",(IF(C5="","",RANK(C5,$C$5:$C$11,0))),IF(C5="","",(IF(C5="","",RANK(C5,$C$5:$C$11,0)))-(COUNTIF($C$5:$C$11,""))))
GENERIC FORMULA

=IF(number<>"",(IF(number="","",RANK(number,range,0))),IF(number="","",(IF(number="","",RANK(number,range,0)))-(COUNTIF(range,""))))

ARGUMENTS
number: A number to rank.
range: A range that contains the numbers to rank.

EXPLANATION

This formula uses the IF, RANK and COUNTIF functions to rank numbers from a selected list ignoring blank cells.

In this example cell C9 will be ignoring when the formula ranks each of the numbers in range C5 to C11 given cell C9 is blank.

RELATED TOPICS

Related Topic Description Related Topic and Description
Rank list of value How to rank a list of values
Rank ignoring zeros How to rank numbers ignoring zeros (0)

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
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria