Finding the first value that is less than zero

[russhess8]

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?

 

[Max]

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

 

[Jacob Skaria]

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

 

[T. Valko]

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.

 

[T. Valko]

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.