Rank only positive numbers
This tutorial shows how to rank only positive numbers from a range using an Excel formula or VBA
Example: Rank only positive numbers
|
This formula uses the Excel IF, MATCH, SMALL, INDIRECT and COUNTIF functions to rank only positive numbers. This is an array formula, therefore, after you have entered the formula you need to press the Ctrl + Shift + Enter keys at the same time, which will convert the formula into an array formula. When you press the Ctrl + Shift + Enter keys the curly brackets ({}) will appear in front and at the back of the formula. Do not insert the curly brackets manually in the formula.
|
Sub Rank_only_positive_numbers()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'rank only positive numbers from a selected range
For x = 5 To 10
ws.Range("D" & x).FormulaArray = "=IF(C" & x & ">0,MATCH(C" & x & ",SMALL(IF(C5:C10>0,$C$5:$C$10),ROW(INDIRECT(""1:""&COUNTIF(C5:C10,"">0"")))),0),"""")"
Next x
For x = 5 To 10
ws.Range("D" & x).FormulaArray = "=IF(C" & x & ">0,MATCH(C" & x & ",SMALL(IF(C5:C10>0,$C$5:$C$10),ROW(INDIRECT(""1:""&COUNTIF(C5:C10,"">0"")))),0),"""")"
Next x
End Sub
EXPLANATION
This tutorial shows how to rank only positive numbers from a specific range through the use of an Excel formula or VBA.
The formula that is applied in both the Excel and VBA methods to get the rank of only positive numbers from a range uses the IF, MATCH, SMALL, INDIRECT and COUNTIF functions.
FORMULA
=IF(number>0,MATCH(number,SMALL(IF(range>0,range),ROW(INDIRECT("1:"&COUNTIF(range,">0")))),0),"")
=IF(number>0,MATCH(number,SMALL(IF(range>0,range),ROW(INDIRECT("1:"&COUNTIF(range,">0")))),0),"")
ARGUMENTS
number: A number that you rank.
range: A range that contains the numbers to rank.
number: A number that you rank.
range: A range that contains the numbers to rank.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Rank list of value | How to rank a list of values using Excel and VBA | |
| Return most frequently occurring text | How to return most frequently occurring text using Excel and VBA |
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 | |
| MATCH Function | The Excel MATCH function searches for a specified value in a range and returns its position relative to the range | |
| SMALL Function | The Excel SMALL function returns the numeric value from a specified range based on the nth smallest position | |
| ROW Function | The Excel ROW function returns the first row number of the selected reference | |
| INDIRECT Function | The Excel INDIRECT function returns a reference based on a text string | |
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria |