Rank ignoring zeros

This tutorial shows how to rank numbers ignoring zeros (0) through the use of an Excel formula, with the IF, RANK and COUNTIF functions

EXCEL FORMULA 1. Rank ignoring zeros

EXCEL

Hard coded formula
Rank ignoring zeros
Cell reference formula
Rank ignoring zeros
=IF(C5>0,(IF(C5=0,"",RANK(C5,$C$5:$C$11,0))),IF(C5=0,"",(IF(C5=0,"",RANK(C5,$C$5:$C$11,0)))-(COUNTIF($C$5:$C$11,0))))
=IF(C7>$C$4,(IF(C7=$C$4,"",RANK(C7,$C$7:$C$13,0))),IF(C7=$C$4,"",(IF(C7=$C$4,"",RANK(C7,$C$7:$C$13,0)))-(COUNTIF($C$7:$C$13,0))))
GENERIC FORMULA

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

ARGUMENTS
number: The number to rank, ignoring zeros.
range: A range that contains the numbers you want to rank.

GENERIC FORMULA

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

ARGUMENTS
number: The number to rank, ignoring zeros.
range: A range that contains the numbers you want to rank.
zero: Value of zero (0).

EXPLANATION

This formula ranks a set of numbers ignoring zeros (0) with the use of the IF, RANK and COUNTIF functions. It takes into consideration the impact of negative and positive numbers and ranks them appropriately. The ranking order is from highest to lowest number. If a cell is 0 the formula will return a blank cell.
Click on either the Hard Coded or Cell Reference button to view the formula that has the value of zero (0) directly entered into the formula or referenced to a specific cell.

In this example the formula ranks the values in range C5 to C11 (hard coded example) or C7 to C13 (cell reference example) and ignores all cells that comprise entirely of 0.

RELATED TOPICS

Related Topic Description Related Topic and Description
Rank if greater than zero How to rank a number if it is greater than zero (0)
Rank if greater than or equal to zero How to rank a number if it is greater than or equal to zero (0)
If ranked first How to test if a value in a cell is ranked first and return a value if the test is True or False

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