Finding the first value that is less than zero
 

I have a list of values in column b. I want to find the first time that this
value is less than zero. I then want to list the corresponding row in column
a. any suggestions?

Finding the first value that is less than zero
 

Array-enter this expression in say C2,
ie confirm it by pressing CTRL+SHIFT+ENTER:
=INDEX(A2:A5,MATCH(MIN(IF(B2:B5<0,B2:B5)),IF(B2:B5 <0,B2:B5),0))
Adapt the ranges to suit your actual data extents in cols A and B

Success? Punch it here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"russhess8" wrote:
I have a list of values in column b. I want to find the first time that this
value is less than zero. I then want to list the corresponding row in column
a. any suggestions?

Finding the first value that is less than zero
 

Try the below.Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula}"

=INDEX(A1:A100,MIN(IF(B1:B100<0,ROW(B1:B100))))

If this post helps click Yes
---------------
Jacob Skaria


"russhess8" wrote:

I have a list of values in column b. I want to find the first time that this
value is less than zero. I then want to list the corresponding row in column
a. any suggestions?

Finding the first value that is less than zero
 

Try this...

Assume your data is in the range B2:B20

Array entered** :

=MATCH(TRUE,B2:B20<0,0)+ROW(B2)-1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"russhess8" wrote in message
...
I have a list of values in column b. I want to find the first time that
this
value is less than zero. I then want to list the corresponding row in
column
a. any suggestions?

Finding the first value that is less than zero
 

Hmmm...

I then want to list the corresponding row in column a.

I interpret that to mean you want the formula entered in some cell in column
A that returns the *row number* of the cell in column B that meets the
condition.

From reading the other replies I'm the only one that interprets it in this
way.

If you do want to return the corresponding value from column A where column
B is <0 then try this array formula** :

=INDEX(A2:A20,MATCH(TRUE,B2:B20<0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this...

Assume your data is in the range B2:B20

Array entered** :

=MATCH(TRUE,B2:B20<0,0)+ROW(B2)-1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"russhess8" wrote in message
...
I have a list of values in column b. I want to find the first time that
this
value is less than zero. I then want to list the corresponding row in
column
a. any suggestions?