first and last occurance
 

in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0?

They can be set in B2 and B3.
Thanks for help

first and last occurance
 

Hi,

To find the first value less than zero use this array formula

=INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1)

To find the last negative number use this array formula

=LOOKUP(2,1/(A1:A1000<0),A1:A1000)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Darius" wrote:

in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0?

They can be set in B2 and B3.
Thanks for help

first and last occurance
 

Thanks but the first eqiuation bounce me back with #N/A and the second
equation give me the last occurance of value less than zero but not the row
number which it is located.


"Mike H" wrote:

Hi,

To find the first value less than zero use this array formula

=INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1)

To find the last negative number use this array formula

=LOOKUP(2,1/(A1:A1000<0),A1:A1000)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Darius" wrote:

in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0?

They can be set in B2 and B3.
Thanks for help

first and last occurance
 

ok both formula works and give some numbers but not the right one. I am using
this:

=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1)
Ctrl+Enter+Shift

and:


=LOOKUP(2,1/(E2:E3286<0),E2:E3286)

Ctrl+Enter+Shift

beyond wrng data still have problem with row number of the values these two
equation pick
"Mike H" wrote:

Hi,

To find the first value less than zero use this array formula

=INDEX(A1:A1000,MATCH(TRUE,A1:A1000<0,0),1)

To find the last negative number use this array formula

=LOOKUP(2,1/(A1:A1000<0),A1:A1000)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike





"Darius" wrote:

in range a2:a3500 how to find the row number of first cell which its content
value is less than 0.0 and last value which is less than 0.0?

They can be set in B2 and B3.
Thanks for help

first and last occurance
 

Hello Darius,

Array-enter
=1+MATCH(TRUE,A2:A3500<0,0)
resp.
=LOOKUP(2,1/(A2:A3500<0),ROW(A2:A3500))

Regards,
Bernd

first and last occurance
 

Thanks the location works fine based on your equation but the below formula
does not find the first value in the range which is less than 0.0?/

=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0),1)

"Bernd P" wrote:

Hello Darius,

Array-enter
=1+MATCH(TRUE,A2:A3500<0,0)
resp.
=LOOKUP(2,1/(A2:A3500<0),ROW(A2:A3500))

Regards,
Bernd

first and last occurance
 

Hello Darius,

That's because one param is missing for MATCH.

Array-enter:
=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0,0),1)

Regards,
Bernd

first and last occurance
 

Perfect thanks

"Bernd P" wrote:

Hello Darius,

That's because one param is missing for MATCH.

Array-enter:
=INDEX(E2:E3286,MATCH(TRUE,E2:E3286<0,0),1)

Regards,
Bernd