Rank without ties ignoring zeros

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

EXCEL FORMULA 1. Rank without ties ignoring zeros

EXCEL

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

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

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

GENERIC FORMULA

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

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

EXPLANATION

This formula ranks a set of numbers without ties and ignoring zeros (0) with the use of the IF, RANK and COUNTIF functions. If a list has two or more of the same numbers the formula will provide them with sequential ranking numbers instead of the same rank. It also 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 without duplicates 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 ignoring zeros How to rank numbers ignoring zeros (0)
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
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria