If ranked last
This tutorial shows how to test if a value in a cell is ranked last and return a value if the test is True or False through the use of Excel formulas, with the IF, RANK, COUNT and MIN functions
|
GENERIC FORMULA
=IF(RANK(number,range)=COUNT(range),value_if_true, value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(RANK(number,range)=COUNT(range),value_if_true, value_if_false)
ARGUMENTS EXPLANATION This formula uses the IF, RANK and COUNT functions to assess if a number captured in a cell is ranked last from a list of other numbers. In other words, it tests if the selected number is the smallest number in a range and then returns a value that has been assigned as the true value, alternatively if the number is not the smallest number from a list 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 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 C7 to C11 (cell reference example) to assess which of these numbers is the smallest number and return "Last Place" (hard coded example) or value in cell C4 (cell reference example). For the numbers that are not the smallest the formula will return a blank cell through the use of double quotation marks (""). |
|
GENERIC FORMULA
=IF(number=MIN(range),value_if_true, value_if_false)
ARGUMENTS GENERIC FORMULA
=IF(number=MIN(range),value_if_true, value_if_false)
ARGUMENTS EXPLANATION This formula uses the IF and MIN functions to assess if a number captured in a cell is ranked last from a list of other numbers. In other words, it tests if the selected number is the smallest number in a range and then returns a value that has been assigned as the true value, alternatively if the number is not the smallest number from the list 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 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 C7 to C11 (cell reference example) to assess which of these numbers is the smallest number and return "First Place" (hard coded example) or value in cell C4 (cell reference example). For the numbers that are not the smallest the formula will return a blank cell through the use of double quotation marks (""). |
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| 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 through the use of an Excel formula | |
| Lookup nth largest value | How to lookup the nth largest value in a range through the use of an Excel formula or VBA | |
| Rank list of value | How to rank a list of values through the use of an Excel formula or VBA |
| 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 | |
| MIN Function | The Excel MIN function returns the smallest value from a specified range of numeric values | |
| COUNT Function | The Excel COUNT function returns the number of cells that contain numeric values in a specified range |