Extract records containing negative numbers
Table of Contents
Extract negative values and adjacent cells (array formula)
Extract negative values and adjacent cells (Excel Filter)
Array formula in B23:
copied down as far as needed and then copied to the right as far as needed.
Explaining array formula in cell B23
Step 1 - Filter negative values and return their corresponding row number
IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({1;5;0;-2;-5;2;4;4;-3;-4;-4;1;-1;0;-5;1}<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, "")
becomes
IF({FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16}, "")
and returns
{"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}
Step 2 - Return the k-th smallest row number
SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1))
becomes
SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, ROW(A1))
becomes
SMALL({"";"";"";4;5;"";"";"";9;10;11;"";13;"";15;""}, 1)
and returns 4.
Step 3 - Return value from cell range
INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))
becomes
INDEX($B$3:$D$18, 4, 1)
becomes
INDEX({"MARGARET","ROBINSON",1; "DAVID","DAVIS",5; "MICHAEL","JONES",0; "MARIA","GARCIA",-2; "LISA","RODRIGUEZ",-5; "PATRICIA","JACKSON",2; "MARY","THOMAS",4; "THOMAS","TAYLOR",4; "MICHAEL","JONES",-3; "RICHARD","MILLER",-4; "JOSEPH","MOORE",-4; "LINDA","WHITE",1; "BARBARA","HARRIS",-1; "MARY","THOMAS",0; "JOHN","JOHNSON",-5; "DOROTHY","CLARK",1} 4, 1)
and returns "Maria" in cell B23.
Get excel sample file for this tutorial.
extract negative values and adjacent cells.xls
(Excel 97-2003 Workbook *.xls)
Extract negative values and adjacent cells (Excel Filter)
- Select B2:D18
- Press with left mouse button on "Data" tab
- Press with left mouse button on "Filter" on the Ribbon
- Press with left mouse button on Black triangle in cell D2
- Select "Number Filters"
- Press with left mouse button on "Less Than..."
- Type 0 (zero)
- Press with left mouse button on OK!
Functions in this article:
MATCH(lookup_value;lookup_array, [match_type])
Returns the relative position of an item in an array that matches a specified value
IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference) returns the rownumber of a reference
COLUMN(reference)
returns the column number of a reference
Filter records category
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Lookup with criteria and return records.
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]
Question: I have a list and I want to filter out all rows that have a value (Column C) that […]
This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]
Question: How to filter rows using dates? Answer: In this post I will describe how to: Filter rows using array […]
Murlidhar asks: How do I search text in cell and use a date range to filter records? i.e st.Dt D1 […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
7 Responses to “Extract records containing negative numbers”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi,
the formula for Extracting negative values and adjacent cells (array formula) is very useful for me and saved me a lot of time. Now I have one additional question - how can I change the formula to extract only positive value?
Thanks for help
Milan
good...but can you explan...itin detail
yashwant
I have added an explanation to this post.
Can you return this values in ascending or descending order
Yes, it is possible.
Ascending:
Descending:
These dynamic array formulas contain two new functions available for Excel 365 subscribers:
How to use the FILTER function
How to use the SORT function
Thank you for this article.
Can you please help in how can I get data in Descending order using this your formula.
{INDEX($B$3:$D$18, SMALL(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))}
Abhijit Mishra
=INDEX($B$3:$D$18, LARGE(IF($D$3:$D$18<0, ROW($D$3:$D$18)-MIN(ROW($D$3:$D$18))+1, ""), ROW(A1)), COLUMN(A1))