Rank only negative numbers

This tutorial shows how to rank only negative numbers from a range using an Excel formula or VBA

Example: Rank only negative numbers

Rank only negative numbers

METHOD 1. Rank only negative numbers using Excel formula

EXCEL

=IF(C5<0,MATCH(C5,LARGE(IF(C5:C10<0,C5:C10),ROW(INDIRECT("1:"&COUNTIF(C5:C10,"<0")))),0),"")
This formula uses the Excel IF, MATCH, LARGE, INDIRECT and COUNTIF functions to rank only negative 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.

METHOD 1. Rank only negative numbers using VBA

VBA

Sub Rank_only_negative_numbers()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'rank only negative numbers from a selected range
For x = 5 To 10
ws.Range("D" & x).FormulaArray = "=IF(C" & x & "<0,MATCH(C" & x & ",LARGE(IF(C5:C10<0,$C$5:$C$10),ROW(INDIRECT(""1:""&COUNTIF(C5:C10,""<0"")))),0),"""")"
Next x

End Sub

Explanation about how to rank only negative numbers

EXPLANATION

EXPLANATION

This tutorial shows how to rank only negative 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 negative numbers from a range uses the IF, MATCH, LARGE, INDIRECT and COUNTIF functions.
FORMULA
=IF(number<0,MATCH(number,LARGE(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.

RELATED TOPICS

Related Topic Description Related Topic and Description
How to rank only positive numbers from a range using Excel and VBA
How to rank a list of values using Excel and VBA
How to return most frequently occurring text using Excel and VBA

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
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
The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
The Excel LARGE function returns the numeric value from a specified range based on the nth largest position
The Excel ROW function returns the first row number of the selected reference
The Excel INDIRECT function returns a reference based on a text string
The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria