Rank if greater than zero
This tutorial shows how to rank a number if it is greater than zero (0) through the use of an Excel formula, with the IF and RANK functions
Hard coded formula
Cell reference formula
|
=IF(C5>0,RANK(C5,$C$5:$C$11),"")
=IF(C7>$C$4,RANK(C7,$C$7:$C$13),"")
|
GENERIC FORMULA
=IF(number>0,RANK(number,range),"")
ARGUMENTS GENERIC FORMULA
=IF(number>zero,RANK(number,range),"")
ARGUMENTS EXPLANATION This formula uses the IF and RANK functions to rank numbers from a selected range that are greater than 0, alternatively, 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) to assess which of these numbers is larger than 0 and rank this number against other numbers in the range that are greater than 0. 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 |
|---|---|---|
| Lookup nth largest value | How to lookup the nth largest value in a range | |
| Find largest n numbers | How to find largest n numbers | |
| 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 |